Dump of SQL Server insert data

Question

Is there a free tool that will allow you to dump the data (not the structure) of an MS-SQL Express (2005) table as INSERT statements to a flat file?

This commercial product seems to do what I have in mind. I've tried using SqlDump but my full server name will not fit in its SQL Server Name field.

Answer 1

I think This will work for you, but you'll have to run it for each table.

Answer 2

This should do it http://sourceforge.net/projects/vwg-ent-man

Answer 3

I know this is not what you had in mind, and the tools suggested look promising but if the data is going from one SQL Server to another, a low overhead alternative would be using bcp. Important options below include the -E for retaining identity info, and the -n for using native format. Executing the first statement would let build a batch file to dump all the data to file, the second statement will create the bcp ins.

Select 'bcp ' + Table_Catalog  + '..' + 
Table_Name + ' out ' + Table_Name 
+ '.bcp  -S ServerName -U userid -P password -n '
from information_schema.tables 
where table_type = 'BASE TABLE'

Select 'bcp ' + Table_Catalog  + '..' + Table_Name 
+ ' in .\' + Table_name + 
'.bcp  -S .\ -U userid -P password -n -E '
from information_schema.tables 
where table_type = 'BASE TABLE'

Sample output

bcp out command

bcp master..tblDepartments out tblDepartments.bcp  -S ServerName -U
userid -P password -n

bcp in command

bcp master..tblDepartments in .\tblDepartments.bcp  -S .\ 
-U userid -P password -n -E

Answer 4

tablediff is a free tool that comes with MS SQL Server. As long as your source is the table that you want to generate inserts from, and you've got another empty table that you want to generate inserts to, it'll create exactly the file that you're looking for.

BCP is probably the better choice of a utility for moving data - but it won't generate the actual DML insert statments like it sounds you're looking for.

I'd also check out the Database Publishing Wizard. I've not used this, but it should be able to script both your schema and data - and from there you could cut up the scripts that it generates to be what you're looking for.

See this link for more info on the tablediff utility.

See this link for the download of the database publishing wizard.

Answer 5

try the free SSMS Tools Pack which is an add-in for SSMS and SSMS Express. it enables you to generate insert scripts for a table, dte whole database or just for the query results.

Answer 6

I have a production database on MSSQLServer and web page is using MYSQL server. Is there any tool that can help me to copy table or dump a few tables from production table(MSSQL) to mySQL table. I want the scheduler or timer to handle the process (migration) every night.