Export table into text file with create statements

Question

Post reply

Export table into text file with create statements

Matthew24

SSCommitted

Points: 1736
More actions
May 4, 2009 at 8:12 pm

#204271

Hi,
I have noticed that using Studio Express, I can script a table 'CREATE TO' so I can duplicate a table across other databases.
Is there a method to not only get the SQL statements to create the table, but for it to 'export' on INSERT INTO all the data.
I need to update some databases across unconnected servers with a new and already populated table.
Many thanks,
Matt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eswin SSCrazy Eights Points: 8320 More actions · Answer 1

If the servers are connected over network you can use "Sql server Import and Export wizard" to export data.

Tanx 😀

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

I don’t believe there is inbuilt functionality to script out the Insert statements for all the data in a Single Table, but there are third party tools which can do this e.g. from RedGate.

By “Unconnected Servers” I assume you mean they are not even on the same LAN, rather than just not Linked?

Do you want to copy the Table data programmatically e.g. if you have to do it frequently or if there are lots of Servers to update, or can the process be manual?

For a manual process you could consider restoring a backup of your source DB to a temporary DB on the target server and then copy the Table across (e.g. via the Import/Export Wizard).

For an automated process then it is probably best to export the data to a flat file and re-import it on the Target Server (Naturally, after executing the Create Table statement). You could use a built in tool like SQLCMD, BCP or SSIS for the data import/export.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

hayzer Say Hey Kid Points: 713 More actions · Answer 3

John Marsh (5/5/2009)
Hello,
I don’t believe there is inbuilt functionality to script out the Insert statements for all the data in a Single Table, but there are third party tools which can do this e.g. from RedGate.
By “Unconnected Servers” I assume you mean they are not even on the same LAN, rather than just not Linked?
Do you want to copy the Table data programmatically e.g. if you have to do it frequently or if there are lots of Servers to update, or can the process be manual?
For a manual process you could consider restoring a backup of your source DB to a temporary DB on the target server and then copy the Table across (e.g. via the Import/Export Wizard).
For an automated process then it is probably best to export the data a flat file and re-import it on the Target Server (Naturally, after executing the Create Table statement). You could use a built in tool like SQLCMD, BCP or SSIS for the data import/export.
Regards,
John Marsh

enough said!

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

This may be helpful

http://vyaskn.tripod.com/code.htm#inserts

Madhivanan

Failing to plan is Planning to fail