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

"sqlnewbie" <sqlnewbie@yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@newssvr25.news.prodigy. 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.Customers

/* Insert only data which isn't already there */

use Data02
go

insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)

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