Link to home
Start Free TrialLog in
Avatar of Scott Milner
Scott MilnerFlag for United States of America

asked on

I'm looking to copy specific data from one SQL table to another, and need help with the query.

Hello Experts!

This is probably a simple request, but I'm not a dba, so I'm looking for help.

Our ERP system uses SQL 2012.

I have a table called InventTable with our part numbers (ITEMID) and their associated information in it.  Some, but not all, of these part numbers have a upc code (UPCCODE) associated with them.

I have another table called ItemBarCode that is currently unpopulated.  It has columns for ITEMID and ITEMBARCODE (which is the same as the UPCCODE in the InventTable.

What I'd like to do is look at each line in InventTable.  If the ITEMID has an associated UPCCODE value, I'd like both the ITEMID and its associated UPCCODE copied to the ItemBarCode table, with the data ending up in the ITEMID and ITEMBARCODE fields, respectively.  If the  ITEMID in the InventTable field doesn't have a UPCCODE, that line should be skipped.

All values are copied, not moved... the InventTable should not be modified in any way.

Can anyone help me with how I would approach this problem?

Thanks,

Scott
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What you want is an UPDATE command:
UPDATE ibc
SET ibc.ITEMBARCODE = it.UPCCODE
FROM ItemBarCode ibc 
	INNER JOIN InventTable it ON ibc.ITEMID = it.ITEMID
WHERE it.UPCCODE IS NOT NULL

Open in new window

Re read your question and if ItemBarCode table is really empty then after all you'll need an INSERT command to copied the desired records:
INSERT INTO ItemBarCode (ITEMID, ITEMBARCODE) 
SELECT ITEMID, UPCCODE
FROM InventTable 
WHERE UPCCODE IS NOT NULL

Open in new window

Avatar of Scott Milner

ASKER

Thanks Vitor!

How might I modify the query to just run the first line or two (to verify that it's working as I expect) before unleashing it on the entire table?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks so much for the help, Vitor... it worked perfectly!