You have a table named MyTable that contains X number of rows. You need a SQL script that performs the CREATE TABLE command and INSERTs the X rows. There are several ways to accomplish this - this blog post is just one way. SQL Server Managament Studio does a great job of generating a CREATE TABLE script but not the INSERT commands. Following is a quick procedure to generate the INSERT commands using the SQL Server command-line utility named tablediff.exe. In SQL Server 2008 this application is located here - assuming default installation (C:\Program Files\Microsoft SQL Server\100\COM).
The tablediff.exe utility is normally used for replication synching but can be repurposed to generate INSERT statements.
First, create an empty copy of MyTable.
SELECT *
INTO dbo.MyTableCopy
FROM dbo.MyTable
WHERE 0 = 1;
Next execute the following command from a Windows command-line. You will need to change directories to where the tablediff utility is located first.
C:\Program Files\Microsoft SQL Server\100\COM>tablediff.exe -sourceserver MyServer -sourcedatabase MyDatabase -sourcetable MyTable -sourceuser sa -sourcepassword sa -destinationserver MyServer -destinationdatabase MyDatabase -destinationtable MyTableCopy -destinationuser sa -destinationpassword sa -f c:\temp\insert_MyTable.sql
Please note that rather than using named SQL Server logins you can use Windows Authentication and exclude the user/password values. Also the C:\temp directory may need to be created if it doesn't already exist. The process fails if the insert_MyTable.sql file already exists.
The insert_MyTable.sql file will contain the INSERT statements for the X number of rows in the original MyTable table. You will have to open the file in a text editor and global replace MyTableCopy with MyTable. If you combine this INSERT command list with the CREATE TABLE script from SSMS you have what you need to recreate the table and the data contained therein.
For more information about the tablediff.exe utility either go to
SQL BOL or type in the following at the Windows command-line.
tablediff.exe /?
Please share any feedback that you may have. Thanks!