SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help on sqlquery instead of cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 09/13/2013 :  21:35:30  Show Profile  Reply with Quote
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)



label:

l_id(int)(Identity),name(nvarchar(100),uniquecode(nvarchar(100),p_id(int),description nvarchar(1000)




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

Edited by - sqllover on 09/13/2013 21:54:07

Ifor
Aged Yak Warrior

590 Posts

Posted - 09/16/2013 :  08:12:14  Show Profile  Reply with Quote

SET IDENTITY_INSERT Product ON;

INSERT INTO Product (p_id, name, uniquecode, [description])
SELECT DISTINCT p_id, name, uniquecode, [description]
FROM label L
WHERE NOT EXISTS
(
	SELECT 1
	FROM Product P
	WHERE P.uniqueCode = l.uniqueCode
);

SET IDENTITY_INSERT Product OFF;

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 09/16/2013 :  09:50:24  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000