Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear All,

I am given the task to modify the stored procedure that already exists in the database.

Here's the SP:

SQL
USE [FTA]
GO
/****** Object:  StoredProcedure [dbo].[SP_UploadFTAReport]    Script Date: 01/28/2011 10:13:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROC [dbo].[SP_UploadFTAReport]-- 1
(
@UploadedBy BIGINT
)
AS
BEGIN
SET NOCOUNT ON
DELETE FROM FTA_ReportFileDump WHERE MobileNo IS NULL

INSERT INTO dbo.FTA_Report
select  A.MobileNo,A.CustomerName,A.Product,A.Suk_Type,A.FTR_Date,A.FTA_Date,
A.Sim_No,A.IMSI_No,A.Retailer_No,A.RetailerName,A.MacMobile_No,A.MacPrimary,
A.MacLocation,A.HUB,A.CombinedZone,A.TM_Name,A.TM_Number,A.RETAILER_SMS_DATE,
A.CUSTOMER_SMS_DATE,A.[TAT/Hrs],A.[TAT/Mins],A.MinuteWiseData,0,@UploadedBy,GETDATE()
FROM dbo.FTA_ReportFileDump A where not exists
(select 1 from FTA_ReportTbView B
where A.MobileNo = B.MobileNo)




TRUNCATE TABLE FTA_ReportFileDump

END

-----------------------

Now, in between "INSERT" and "TRUNCATE" I need to write the query opposite of the above select statement to insert the values into the
VB
dbo.FTA_Report

table. That means, a query which doesn't satisfy the above select statement.

I hope u understood what I am trying to ask u...

Please anyone help me..


Regards,

Raj
Posted
Comments
Sunasara Imdadhusen 28-Jan-11 0:00am    
Not clear!!
Raj.rcr 28-Jan-11 0:07am    
Can u Plz tell me what is the use of "not exists" ?
Nithin Sundar 28-Jan-11 0:24am    
In your stored procedure, the not exists condition seems to be dependant on the value retrieved from the FTA_ReportTbView table. The value returned by that subquery will be checked against the original query and records which match that value will be filtered out. In other words, all records which don't have that value will be shown.

Seriously though, you should have understood that by the name itself which says "not exists".
T.Saravanann 28-Jan-11 0:08am    
Your question is not clear to understand...
thatraja 28-Jan-11 0:21am    
Rephrase your question

Hi Raj,

In your condition 'not exists' means in 'dbo.FTA_ReportFileDump' table value already there in 'dbo.FTA_Report' table with your condition 'A.MobileNo = B.MobileNo' means apart from that records only inserted into 'dbo.FTA_Report' table.
 
Share this answer
 
Alright Raj. Based on your comment, you want to insert only into those records which haven't been selected by the existing mechanism right?

You can only select records based on conditions. For columns however you must either use the "*" operator(which will select all the records) or specify the column names by yourself which you want to show in your result.

For selecting records, use a "where exists" instead of "where not exists". Basically the opposite of the existing behavior since this time all records containing the parameter returned by the subquery will be selected.
 
Share this answer
 
Comments
Raj.rcr 28-Jan-11 1:36am    
Ohh Nitin ji, I already tried with where exists. But My question is how/where to display the columns which are not inserted? Plz try to undrstnd wht I ma saying. Even I dnt want "copy-paste", but I am not getting the idea as I i have been trying from the past 2 hours.
Nithin Sundar 28-Jan-11 3:54am    
Hmm...well there MIGHT be a way. When you don't insert into certain columns null values get inserted in them right(if they are nullable)? Perhaps you can try something with that.

I'm afraid that as far as showing specific columns goes, I'm not aware of anything else except * (to show all columns and rows) and specifying the column names themselves.
Raj.rcr 28-Jan-11 5:06am    
I got it. I just created a temporary table to display the columns which are not inserted in FTA_Report table.

This is the code:
INSERT INTO dbo.FTA_ReportFileDumpNew
select A.*,GETDATE() from dbo.FTA_ReportFileDump A where exists
(select 1 from FTA_ReportTbView B
where A.MobileNo = B.MobileNo)

Thank you sooo much..
Nithin Sundar 28-Jan-11 5:13am    
That's cool! It's great to see that you got the solution. Enjoy! :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900