May 3, 2010 at 4:47 pm
See tables and sample data below.
#Lists is a table which I periodically need to add records to or remove records from. #ListUpdate is the source data. My method has been to Delete eveything from #Lists and then add records like
DELETE ListName FROM #Lists
INSERT INTO #Lists
SELECT
ListName,
ListMember
FROM #ListUpdate
I'd prefer not to Delete and then Insert all. I'd prefer to delete and and insert the specific records that need it.
This is all I can think of right now but it seems fairly lame.
INSERT INTO #Lists
SELECT
ListName,
ListMember
FROM #ListUpdate
WHERE ListName + ListMember NOT IN
(
SELECT ListName + ListMember FROM #Lists
)
...and then something similar for the Delete.
Please help me write better code.
CREATE TABLE #Lists
(
ListName char(1),
ListMember int
)
INSERT INTO #Lists
SELECT 'A',1 UNION
SELECT 'A',2 UNION
SELECT 'A',3 UNION
SELECT 'B',4 UNION
SELECT 'B',1 UNION
SELECT 'C',7 UNION
SELECT 'C',8
CREATE TABLE #ListUpdate
(
ListName char(1),
ListMember int
)
INSERT INTO #ListUpdate
SELECT 'A',1 UNION
SELECT 'A',2 UNION
SELECT 'A',5 UNION
SELECT 'B',4 UNION
SELECT 'C',1 UNION
SELECT 'C',7 UNION
SELECT 'C',8
SELECT * FROM #Lists
SELECT * FROM #ListUpdate
DROP TABLE #Lists
DROP TABLE #ListUpdate
May 3, 2010 at 10:57 pm
I dint exactly understand what you wnat to do, but as far i could comprehend from your post, here is a set of code that might interest you.
1. INSERT only new records present in #ListUpdate which are not present in #Lists table
INSERT INTO #Lists (ListName, ListMember)
SELECT
A.ListName, B.ListMember
FROM
#ListUpdate A
LEFT JOIN
#Lists B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
WHERE
(A.ListName IS NULL AND A.ListMember IS NULL)
2. DELETE data from #Lists that are present in both #ListUpdate and #Lists tables
DELETE A
FROM
#Lists A
INNER JOIN
#ListUpdate B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
Hope this helps you! Post us much clearer question and we will give you exactly what you wanted!
Cheers!!
May 4, 2010 at 2:24 am
ColdCoffee (5/3/2010)
I dint exactly understand what you wnat to do, but as far i could comprehend from your post, here is a set of code that might interest you.1. INSERT only new records present in #ListUpdate which are not present in #Lists table
INSERT INTO #Lists (ListName, ListMember)SELECT
A.ListName, B.ListMember
FROM
#ListUpdate A
LEFT JOIN
#Lists B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
WHERE
(A.ListName IS NULL AND A.ListMember IS NULL)
2. DELETE data from #Lists that are present in both #ListUpdate and #Lists tables
DELETE AFROM
#Lists A
INNER JOIN
#ListUpdate B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
Hope this helps you! Post us much clearer question and we will give you exactly what you wanted!
Cheers!!
i spent good time to understand the exact requirement and finally got that above solution will work for it.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2010 at 2:25 am
Additionally you need to take care about indexes in the concerned tables
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2010 at 1:52 pm
ColdCoffee (5/3/2010)
quote]
So I want to
1. INSERT records present in #ListUpdate which are not present in #Lists table
2. DELETE records from #Lists that that are no longer in #ListUpdate
So conceptually I want to truncate #Lists and Insert all records from #ListUpdate into #Lists. This would give me my desired results. From a coding/practical perspective I want to only insert and delete the minimum records needs to 'sync' #Lists with #ListUpdate.
May 4, 2010 at 8:33 pm
emily-1119612 (5/4/2010)
So conceptually I want to truncate #Lists and Insert all records from #ListUpdate into #Lists. This would give me my desired results. From a coding/practical perspective I want to only insert and delete the minimum records needs to 'sync' #Lists with #ListUpdate.
Emily, going by your concept, dint the code i posted dint do what you wanted??
And thanks Bhuvnesh, for confirming on the code!
May 5, 2010 at 10:18 am
Hello Cold Coffee and thanks.
No its not getting my desired results. The SELECT statement for the INSERT should return these records:
A,5
C,1
These are the records in #ListUpdate that are not in #Lists. These should be inserted.
The records to be deleted from #Lists are:
A,3
B,1
These are records in #Lists but they are not in #ListUpdate. These should be deleted from #Lists .
May 5, 2010 at 10:31 am
Buddy, small tweaks in the code will get you there!
For this requirement,
The SELECT statement for the INSERT should return these records:
A,5
C,1
These are the records in #ListUpdate that are not in #Lists. These should be inserted.
This is the code:
SELECT
A.ListName, A.ListMember
FROM
#ListUpdate A
LEFT JOIN
#Lists B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
WHERE
(B.ListName IS NULL AND B.ListMember IS NULL)
And for this,
The records to be deleted from #Lists are:
A,3
B,1
These are records in #Lists but they are not in #ListUpdate. These should be deleted from #Lists
this is the code:
DELETE A
FROM
#Lists A
LEFT JOIN
#ListUpdate B
ON
(A.ListName = B.ListName AND A.ListMember = B.ListMember)
WHERE
(B.ListName IS NULL AND B.ListMember IS NULL)
Hope this helps you, emily!
Revert back to us if the code performed your request!
Cheers!
May 5, 2010 at 11:17 am
Yes looks good. Thanks!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply