copy information from one table to another

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 2008 Forums

Transact-SQL (2008)

copy information from one table to another

Author

Topic

zukipower
Starting Member

10 Posts

Posted - 2010-05-04 : 08:46:53

Hello everyone,

I have a question related to copying data from one table to another with a certain information.

What i need to do is to copy over 4 or 5 columns from one table to another IF the spare part in the table that should be updated has the same spare part number as in the other table. This means that i should not copy the entire columns but certain lines. How can i do this. I have tried to write a script that looks like this;

INSERT INTO TBL_MAS_ITEM_MASTER (ITEM_DISC_CODE, ITEM_DISC_CODE_BUY) SELECT ITEM_DISC_CODE, ITEM_DISC_CODE_BUY FROM TBL_SPR_GLOBALSPAREPART
where ID_ITEM = TBL_SPR_GLOBALSPAREPART.ID_ITEM and TBL_SPR_GLOBALSPAREPART.ID_ITEM != null

Note that i only have 2 of the 4 or 5 columns in the script, but might add those later.

Can anyone in here please give me a pointer in the right direction of what i am doing wrong? I'm pretty new to SQL so please bear with me if im doing this totally wrong. :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-04 : 08:55:41

Please give table structure, example data and wanted result.
Then it is much easier to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

zukipower
Starting Member

10 Posts

Posted - 2010-05-04 : 10:07:48

tbl_mas_item_master contains several columns. The main column is called id_item which gives a list of spare parts numbers. the other table i refer to called TBL_SPR_GLOBALSPAREPART.ID_ITEM is another table with the same column. these columns can show the same list if they are updated with the same data. The difference is that the TBL_SPR_GLOBALSPAREPART.ID_ITEM is usually updated with new information regularly, showing a new price for the spare part number, discount, cost price etc in the containing columns in the table. This updated information i would like to update over to my other table called TBL_MAS_ITEM_MASTER. But it should only update the info in TBL_MAS_ITEM_MASTER and not copy the entire TBL_SPR_GLOBALSPAREPART over to the other table, which means the spare part needs to exist in the TBL_MAS_ITEM_MASTER for it to be updated by the information in the columns chosen from TBL_SPR_GLOBALSPAREPART.

I hope you understand what i mean with this. It's quite complicated. :P

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-04 : 12:54:29

So there are always matching entries in both tables?
There are no duplicate entries?
Then you can adapt the following example for your needs where you have to correct table names and column names:


update m
set price=g.price,
    cost=g.cost,
    discount=g.discount
from item_master_table m
join item_global_table g
on m.part_id=g.part_id

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:03:05

does that mean there wont be any new information appearing on TBL_SPR_GLOBALSPAREPART? if yes, how would you handle them? anyways i feel like this is a potential scenario for applying MERGE statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zukipower
Starting Member

10 Posts

Posted - 2010-05-05 : 03:34:34

The TBL_SPR_GLOBALSPAREPART table can get new information, but then i would need to run the script again. to compare and update the other table as well with the new information if the id_item column has the same spare part number in both tables.

Of what i can see, it looks like Webfred are into something here.
I will try that out and see how it would look. :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 03:38:46

OK, please come back and let us know.

No, you're never too old to Yak'n'Roll if you're too young to die.

zukipower
Starting Member

10 Posts

Posted - 2010-05-06 : 02:50:15

I can now confirm that it worked like a charm! :D

I just changed it a bit so it would match the fields i needed, so it looked like this:

update m
set item_price=g.ITEM_PRICE,
m.ITEM_DISC_CODE=g.ITEM_DISC_CODE,
m.ITEM_DISC_CODE_BUY=g.ITEM_DISC_CODE_BUY,
m.COST_PRICE1=g.COST_PRICE1,
m.COST_PRICE2=g.COST_PRICE2,
m.BASIC_PRICE=g.BASIC_PRICE,
m.FLG_CALC_PRICE=g.FLG_CALC_PRICE
from TBL_MAS_ITEM_MASTER m
join TBL_SPR_GLOBALSPAREPART g
on m.ID_ITEM=g.ID_ITEM where m.ID_MAKE='VO'

Thank you very much for your feedback everyone, i really appreciate your help! :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-06 : 02:58:02

It depends on how much rows are in your tables.
Maybe you can speed it up:


update m
set item_price=g.ITEM_PRICE,
    m.ITEM_DISC_CODE=g.ITEM_DISC_CODE,
    m.ITEM_DISC_CODE_BUY=g.ITEM_DISC_CODE_BUY,
    m.COST_PRICE1=g.COST_PRICE1,
    m.COST_PRICE2=g.COST_PRICE2,
    m.BASIC_PRICE=g.BASIC_PRICE,
    m.FLG_CALC_PRICE=g.FLG_CALC_PRICE
from TBL_MAS_ITEM_MASTER m
join TBL_SPR_GLOBALSPAREPART g
     on m.ID_ITEM=g.ID_ITEM where and m.ID_MAKE='VO'

No, you're never too old to Yak'n'Roll if you're too young to die.

zukipower
Starting Member

10 Posts

Posted - 2010-05-06 : 06:10:03

There might be from about 10000 rows and up to perhaps a million or so. At this point it is only about 350 000 rows. But the faster it goes, the better :)

Question; Do I need to set m. in front of all the column names i want to set lie the g. columns? Or will those fields assume that these columns are lying in the m table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-06 : 06:57:52

In my experience there is no need to set the alias in front of the columns that should be updated.
But also it is not a mistake to do it.

No, you're never too old to Yak'n'Roll if you're too young to die.

zukipower
Starting Member

10 Posts

Posted - 2010-05-06 : 09:15:10

OK. At least its good to know! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources