Technical Article

Quick update/insert into one table from another

,

This script will show how quickly update records in one table with information from another or insert records if they are completely new.

/*
This script will show how quickly update records in one table with information from another or insert records, if they are completely new.

Created by Eva Zadoyen
01/07/2002
*/
create table TABLE_MAIN(code_id integer NOT NULL, period integer NOT NULL,empl_id integer NULL ,amount money NULL)
create table TABLE_NEW(code_id integer, period integer,amount money)
GO 
insert into TABLE_MAIN values(105,200108,100,500)
insert into TABLE_MAIN values(100,200108,100,300)
insert into TABLE_MAIN values(100,200109,100,250)
insert into TABLE_MAIN values(105,200109,100,350)
insert into TABLE_MAIN values(100,200110,200,0)
insert into TABLE_MAIN values(150,200110,200,0)

insert into TABLE_NEW values(100,200110,500)
insert into TABLE_NEW values(105,200110,900)
insert into TABLE_NEW values(150,200110,200)
insert into TABLE_NEW values(100,200111,375)
insert into TABLE_NEW values(105,200111,750)
insert into TABLE_NEW values(150,200111,900)

go
select * from TABLE_NEW
select * from TABLE_MAIN
go

/*  
TABLE_MAIN

code_id     period      empl_id     amount                
----------- ----------- ----------- --------- 
105         200108      100         500.0000
100         200108      100         300.0000
100         200109      100         250.0000
105         200109      100         350.0000
100         200110      200         .0000
150         200110      200         .0000


TABLE_NEW

code_id     period      amount                
----------- ----------- ---------- 
100         200110      500.0000
105         200110      900.0000
150         200110      200.0000
100         200111      375.0000
105         200111      750.0000
150         200111      900.0000
*/--update existing in TABLE_MAIN records with new amount from the Table_New
UPDATE  TABLE_MAIN
SET amount= n.amount
 FROM TABLE_MAIN t LEFT OUTER JOIN TABLE_NEW n 
ON t.code_id = n.code_id AND t.period = n.period 
WHERE n.code_id IS NOT NULL

-- 'Insert enries that do not exist already)
go
INSERT INTO  TABLE_MAIN(code_id, period, amount,empl_id )
SELECT  n.code_id , n.period, n.amount,0
FROM  TABLE_NEW n LEFT OUTER JOIN TABLE_MAIN t 
ON t.code_id = n.code_id AND t.period = n.period 
WHERE t.code_id IS  NULL

go


select * from table_main
/*
code_id     period      empl_id     amount                
----------- ----------- ----------- ---------
105         200108      100         500.0000
100         200108      100         300.0000
100         200109      100         250.0000
105         200109      100         350.0000
100         200110      200         500.0000
150         200110      200         200.0000
100         200111        0         375.0000
105         200110        0         900.0000
105         200111        0         750.0000
150         200111        0         900.0000
*/
/*
drop table TABLE_MAIN
drop table TABLE_NEW
*/

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating