Insert, Update, Delete - Working with 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert, Update, Delete - Working with 2 tables

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-12 : 05:10:03

I have 2 tables, both with around 4000 rows:

Table1(productID, name, Table1_Some_Other_Fields)
Table2 (productID, name, Table2_Some_Other_Fields)

I would like to do the following:

(1) Insert into Table2 NEW records from Table1 (only productid and name)
(2) Update existing records in Table2 where table1.productid = table2.productid (only update Name column)

(3) Delete from Table2 where no productid exists in Table1

So basically, I want the same number of records in Table1 and Table2, with same productID and Name, and of course, Table2 contains some additional columns.

Any idea how I would write such SP to do the above..????

kind regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 05:33:34

Before trying this take back up of those two tables and if the updation is wrong restore it

1 Insert into Table2(productID, name) Select productID, name from Table1 T where not Exists(Select * from table2 where productID=T.productID)

2 Update T2 set T2.col1=T1.col1,... from Table1 T1 inner join Table2 T2 on T1.productID=T2.productID

3 Try it yourself based on 2