Copy tables and data from one database to another in SQL Server 2005

Hello,

I have 2 databases. One for production and the other is a live database. I want to schedule a job that copies only the tables and data from the live db to the development db in order to replace the dev data with real data. There are many tables and I need the correct identities in the dev db that will come over from the live db. I will also need to have some sort of control over which tables to copy.
What is the best way to do this as a scheduled job?

Thanks!

Microsoft SQL Server 2005Microsoft SQL Server

Last Comment

yuching

8/22/2022 - Mon

puranik_p

3/2/2009

If both the databases are on the same server, you can simply drop and recreate the tables...

DROP TABLE devdb.dbo.table1
SELECT * INTO devdb.dbo.table1 FROM livedb.dbo.table1
 
DROP TABLE devdb.dbo.table2
SELECT * INTO devdb.dbo.table2 FROM livedb.dbo.table2
 
-- and so on

Open in new window

Sharath S

3/2/2009

Do you want to copy the data for all tables from live db to development db?

yuching

3/2/2009

Just a suggestion, you can create a DTS package (put the sql task to be completed in the DTS package) and schedule the DTS to run on once every day/week

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

ggilal

3/2/2009

ASKER

The databases are not on the same server.

RE DTS:
I had to create several packages since there a too many tables to copy over to the dev db all at once. How can I run them one after the other - in a certain order?

Thanks!

yuching

3/2/2009

you can create each different Transform Data Task/ Execute SQL task and execute then in order.
You can refer below link for DTS

http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/1/
http://articles.techrepublic.com.com/5100-10878_11-1043668.html

ggilal

3/2/2009

ASKER

Thanks for the articles. They were great - but since DTS was replaced with SSIS in 2005 - are all those possibilities still available?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

yuching