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
*/