Exporting and Importing Tables

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

General SQL Server Forums

New to SQL Server Programming

Exporting and Importing Tables

Author

Topic

bevans
Starting Member

10 Posts

Posted - 2012-05-14 : 03:53:16

Hi,
I currently use an automation tool, jenkins, and I would like to know, if possible, how to write a script which will export a table from a database, then allow a restore to happen, then import that table again.
The restore works fine. I would just like to know how to do the export and import.

Thank you

--Is there a way to write SQL script that will export the table to another database, and then import that table back into the other database?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 07:13:49

Assuming you have another scratch database on the server, you can create a table in that database and insert all the data into that table like this:


USE YourProductionDB
GO
SELECT * INTO YourScratchDB.dbo.TempHoldingTable
FROM YourSourceTable;

Then you can delete the data in the original database using:

TRUNCATE TABLE YourSourceTable;

And after you restore it, remove any existing data in the same manner and insert the data from the scratch table:

INSERT INTO YourSourceTable
SELECT * FROM YourScratchDB.dbo.TempHoldingTable

BUT, PLEASE TEST IT. There are many caveats. You may not be able to truncate the table; You may not be able to simply insert the data back if there are identity columns etc. So consider this as just a general suggestion which you will test until you are satisfied before you decide to use it if at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources