"sqlnewbie" <sq*******@yaho o.com> wrote in message
news:k4******** ***********@new ssvr25.news.pro digy.com...
I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS
but wanted a script I could email to a user to run in Query Analyzer.
Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database
I want to overwrite all data in the destination table.
Thanks
/* Replace all data in the destination table */
use Data02
go
truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Cust omers
/* Insert only data which isn't already there */
use Data02
go
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Cust omers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCo l = c2.PrimaryKeyCo l)
Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".
I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.
Simon
