September 21, 2009 at 12:13 pm
Greetings, Thanks if you can help.
I have identical staging and a destination tables. My primary key is a combination of fields ID and Date. My date fields will always have a time of midnight.
My business logic is if the identical record exists in staging then overwrite the record in destination.
Given my sample data I want to insert the record whose value is 1010 into destination and update the existing record in destination whose value is 1007 with the record in staging whose value is 1008.
I am not sure if the best choice is a DELETE and then an INSERT or an INSERT and an UPDATE.
Whatever method I choose I an not sure how to make the DELETE or UPDATE statement effect the correct records.
CREATE TABLE #Test_Destination
(
ID int,
Date datetime,
Value int
CONSTRAINT [PK_Test_Destination] PRIMARY KEY CLUSTERED (ID ASC, Date ASC)
ON [PRIMARY]
)
CREATE TABLE #Test_Staging
(
ID int,
Date datetime,
Value int
CONSTRAINT [PK_Test_STaging] PRIMARY KEY CLUSTERED (ID ASC, Date ASC)
ON [PRIMARY]
)
INSERT INTO #Test_Destination
SELECT 1, '1/1/2009', 1001 UNION
SELECT 2, '1/1/2009', 1003 UNION
SELECT 1, '1/2/2009', 1007 UNION
SELECT 3, '1/1/2009', 1002
INSERT INTO #Test_Staging
SELECT 1, '1/2/2009', 1008 UNION
SELECT 1, '1/3/2009', 1010
SELECT * FROM #Test_Destination
SELECT * FROM #Test_Staging
DROP TABLE #Test_Destination
DROP TABLE #Test_Staging
September 21, 2009 at 12:27 pm
Whatever method I choose I an not sure how to make the DELETE or UPDATE statement effect the correct records.
UPDATE #test_destination
SET VALUE = ts.VALUE FROM #test_destination td
JOIN #test_staging ts
ON td.id = ts.id
AND td.DATE = ts.DATE
DELETE #test_destination
FROM #test_destination td
JOIN #test_staging ts
ON td.id = ts.id
AND td.DATE = ts.DATE
September 21, 2009 at 12:36 pm
i think this could help...:-)
INSERT INTO #Test_Destination
SELECT a.*
FROM #Test_Staging a
left join #Test_Destination b
on a.id=b.id and a.Date=b.Date
where b.id is null
DELETE #Test_Staging
FROM #Test_Staging a
inner join #Test_Destination b
on a.id=b.id and a.Date=b.Date and a.value=b.value
UPDATE #Test_Destination
SET value = b.value
FROM #Test_Destination a
inner join #Test_Staging b
on a.id=b.id and a.Date=b.Date
DELETE #Test_Staging
FROM #Test_Staging a
inner join #Test_Destination b
on a.id=b.id and a.Date=b.Date and a.value=b.value
September 21, 2009 at 2:00 pm
This looks good thanks.
DELETE #Test_Destination
FROM #Test_Destination a
inner join #Test_Staging b
on a.id=b.id and a.Date=b.Date
INSERT INTO #Test_Destination
SELECT a.*
FROM #Test_Staging a
left join #Test_Destination b
on a.id=b.id and a.Date=b.Date
where b.id is null
September 21, 2009 at 6:32 pm
I know this is the 2k5 forum - but if you have the opportunity to use 2k8, you might want to look at MERGE.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply