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.
| Author |
Topic |
|
sqllover
Constraint Violating Yak Guru
338 Posts |
 Posted - 2013-09-13 : 21:35:30
|
I have two tables (label,product) and columns are as follows,Product:p_id (int)(identity),name(nvarchar(100),uniquecode(nvarchar(100)),description(nvarchar(1000) [code]label:l_id(int)(Identity),name(nvarchar(100),uniquecode(nvarchar(100),p_id(int),description nvarchar(1000)[code]My requirement is i have to compare label with product based on uniquecode and if data doesn't exits in prodcut than take data from label and insert into product and get the p_id and update into label table based on uniquecode.Currently i am done with the help of fast forward cursor to loop through records from label and insert into product, get the p_id and update into label table(p_id column). Is there any better way to avoid cursor by writing this logic using sql query to improve the performance.also can it be done using Merge statement? If yer please give me some sample query |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-09-16 : 08:12:14
|
| [code]SET IDENTITY_INSERT Product ON;INSERT INTO Product (p_id, name, uniquecode, [description])SELECT DISTINCT p_id, name, uniquecode, [description]FROM label LWHERE NOT EXISTS( SELECT 1 FROM Product P WHERE P.uniqueCode = l.uniqueCode);SET IDENTITY_INSERT Product OFF;[/code] |
 |
|
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2013-09-16 : 09:50:24
|
| Hi Ifor,thanks for the response. as i said i my earlier thread p_id in label table will be null when the table created. once we insert data into product table based on uniquecode the inserted p_id will updated into label table's pi-id column based on uniquecode. where can i write that logic on your sample query. Appreciate your time on this |
|
|
|
|
|
|
|
|
Need help on sqlquery instead of cursor