Copy a table from one database to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Copy a table from one database to another

Author

Topic

gurvanov
Starting Member

5 Posts

Posted - 2004-10-06 : 06:49:00

Hi,
does anybody know how can I copy a table from one database to another, on the same server. I need to do the same thing that DTS does, but programmatically. In fact I must do it once, the whole table, and after that weekly attach the new data in the second table. There is a column with the import date, so I can filter only the new ones. As there is a way to do it with the DTS , it must be possible to do it with the Transact-SQL, I think.
Can someone help me?
Thank you!
hristo

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-06 : 07:10:39

Right-click the table in QA and select script to new window. Change the DB that the connection is pointing at then run this script to create the table in the other DB.


INSERT INTO [database2].dbo.table
SELECT * FROM [database1].dbo.table

subsequently, schedule a job that fires a stored proc that does something like:


DECLARE @max_date DATETIME
SELECT @max_date = MAX(import_date) FROM [database2].dbo.table

INSERT INTO [database2].dbo.table
SELECT * FROM [database1].dbo.table
WHERE import_date > @max_date

Mark

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-06 : 07:23:16

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40917

gurvanov
Starting Member

5 Posts

Posted - 2004-10-06 : 13:47:13

Thanks, it works.

P.S. Sorry for the double post, I didn't know which forum is the right one to post in

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources