March 1, 2012 at 4:55 pm
Hello All,
I can't figure out my delete statement. See description of what I want my delete statement to do below.
CREATE TABLE #Destination
(
Account char(1),
ReportDate datetime,
DataType varchar(5),
Data int
)
CREATE TABLE #Staging
(
Account char(1),
ReportDate datetime,
DataType varchar(5),
Data int
)
INSERT INTO #Destination
SELECT 'A', '2/29/2012','Type1',5 UNION
SELECT 'A', '2/29/2012','Type2',6 UNION
SELECT 'A', '2/29/2012','Type3',7 UNION
SELECT 'B', '2/29/2012','Type1',5 UNION
SELECT 'B', '2/29/2012','Type2',2 UNION
SELECT 'A', '1/31/2012','Type2',12 UNION
SELECT 'A', '1/31/2012','Type3',31
INSERT INTO #Staging
SELECT 'A', '2/29/2012','Type1',10 UNION
SELECT 'A', '2/29/2012','Type2',11 UNION
SELECT 'A', '2/29/2012','Type3',9
SELECT * FROM #Destination ORDER BY Account,ReportDate
SELECT * FROM #Staging ORDER BY Account,ReportDate
--Delete from #Destination
--Conceptually:
--Delete ever record in table #Destination where the combination of Account and ReportDate exists in table #staging
----I want to us an IN subquery to check for existence but don't know how to adapt it to handle a combination of columns
--Insert into #Destination
INSERT INTO #Destination
SELECT * FROM #Staging
SELECT * FROM #Destination ORDER BY Account,ReportDate
SELECT * FROM #Staging ORDER BY Account,ReportDate
/*
Desired results
ccountReportDateDataTypeData
A2012-01-31 00:00:00.000Type212
A2012-01-31 00:00:00.000Type331
A2012-02-29 00:00:00.000Type110
A2012-02-29 00:00:00.000Type211
A2012-02-29 00:00:00.000Type39
B2012-02-29 00:00:00.000Type15
B2012-02-29 00:00:00.000Type22
*/
DROP TABLE #Destination
DROP TABLE #Staging
March 1, 2012 at 5:39 pm
Try this:
INSERT INTO #Destination
SELECT s.* FROM #Staging s
RIGHT OUTER JOIN #Destination d ON s.Account = d.Account and s.ReportDate = d.ReportDate
WHERE d.Account IS NULL
SELECT *
FROM #Destination
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 5:54 pm
That doesn't seem to insert any records. All records in the staging will be inserted.
My issue is I need to delete all records from the destination table where the combination of account and report date exists in the staging table.
A scenario would be the data in destination is incorrect, the correct data has been loaded into staging. I want to overwrite the incorrect data through an delete and an insert.
My business logic is that if an account and reportdate combination makes it into the staging table, the assumptions is all the data for that same combination of account and reportdate in the destination table is incorrect and should be overwritten.
March 1, 2012 at 5:59 pm
In that case, I think you're going to need to use a MERGE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 7:14 pm
Desired result is same as the Destination table! :pinch:
Question is a paradox 😀
March 1, 2012 at 7:19 pm
If u r using SQL 2008, use this:
SELECT * FROM #Destination;
BEGIN TRAN ;
MERGE #Destination TGT
USING #Staging SRC
ON TGT.Account = SRC.Account AND TGT.ReportDate = SRC.ReportDate
WHEN MATCHED THEN DELETE ;
SELECT * FROM #Destination ;
ROLLBACK TRAN ;
If u r using SQL 2008 and below, use htis
SELECT * FROM #Destination;
BEGIN TRAN ;
DELETE TGT
FROM #Destination TGT
INNER JOIN #Staging STG
ON TGT.Account = STG.Account AND TGT.ReportDate = STG.ReportDate
SELECT * FROM #Destination;
ROLLBACK TRAN ;
March 1, 2012 at 7:39 pm
Merge won't work since there's not a one-to-one relationship between deleted and inserted records.
March 1, 2012 at 7:43 pm
Do you want to delete or insert or both?
You can tweak ur merge to perform all 3 operations in one statement. Please throw some more light on your requirement
March 1, 2012 at 7:48 pm
I want to delete and insert. It looks like your second statement is what I need.
March 1, 2012 at 7:51 pm
Seems simple now that that you point it out to me. Thanks.
CREATE TABLE #Destination
(
Account char(1),
ReportDate datetime,
DataType varchar(5),
Data int
)
CREATE TABLE #Staging
(
Account char(1),
ReportDate datetime,
DataType varchar(5),
Data int
)
INSERT INTO #Destination
SELECT 'A', '2/29/2012','Type1',5 UNION
SELECT 'A', '2/29/2012','Type2',6 UNION
SELECT 'A', '2/29/2012','Type3',7 UNION
SELECT 'B', '2/29/2012','Type1',5 UNION
SELECT 'B', '2/29/2012','Type2',2 UNION
SELECT 'A', '1/31/2012','Type2',12 UNION
SELECT 'A', '1/31/2012','Type3',31
INSERT INTO #Staging
SELECT 'A', '2/29/2012','Type1',10 UNION
SELECT 'A', '2/29/2012','Type2',11 UNION
SELECT 'A', '2/29/2012','Type3',9
SELECT * FROM #Destination ORDER BY Account,ReportDate
SELECT * FROM #Staging ORDER BY Account,ReportDate
DELETE TGT
FROM #Destination TGT
INNER JOIN #Staging STG
ON TGT.Account = STG.Account AND TGT.ReportDate = STG.ReportDate
INSERT INTO #Destination
SELECT * FROM #Staging
SELECT * FROM #Destination ORDER BY Account,ReportDate
/*
Desired results
ccountReportDateDataTypeData
A2012-01-31 00:00:00.000Type212
A2012-01-31 00:00:00.000Type331
A2012-02-29 00:00:00.000Type110
A2012-02-29 00:00:00.000Type211
A2012-02-29 00:00:00.000Type39
B2012-02-29 00:00:00.000Type15
B2012-02-29 00:00:00.000Type22
*/
DROP TABLE #Destination
DROP TABLE #Staging
March 2, 2012 at 11:11 am
This below is not performing very well because of there being multiple joins for each record to be deleted. There would be 9 join records when there are 3 records to be deleted.
DELETE TGT
FROM #Destination TGT
INNER JOIN #Staging STG
ON TGT.Account = STG.Account AND TGT.ReportDate = STG.ReportDate
My Destination table has 33,000+ records with 146 unique account reportdate combinations.
This is actually performing better although the concatenation seems to be a silly way to approach this problem.
DELETE
FROM #Destination
WHERE Account + CONVERT(VARCHAR(8), ReportDate, 112) IN
(
SELECT DISTINCT
Account + CONVERT(VARCHAR(8), ReportDate, 112)
FROM #Staging
)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply