Mass Transfer Data

Here my tips for newbie in sql server to transfer massive data from table A to table B.

example using adventuresworks from sql server 2005 database sample.

select * into table1
from HumanResources.Employee
where employeeid between 1 and 20

i create table1 with the same field as HumanResources.Employee. Above query just an example.

--- use it if you need to update some field of records in table1 that may need to be updated if
-- there's exists on destination table.
update HumanResources.Employee set vacationhours = 24
from HumanResources.Employee a
inner join tabel1 b on a.employeeid=b.employeeid

-- here the main massive insert. the order of the field may the same as the destination table.
-- in case there's different, but some of the them have the same field than you need to add
-- (nmfield1, nmfield2 , .. before select statement ) before the select statement.
-- Its better if you defined the field that you want to insert

insert into tabel1
select a.*
from HumanResources.Employee a
left join tabel1 b on a.employeeid=b.employeeid
where b.employeeid is null

Have a try and good luck .

Happy New Years 2009