转储SQL Server插入数据

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?

是否有一个免费工具允许您将MS-SQL Express(2005)表的数据(而不是结构)作为INSERT语句转储到平面文件?

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.

这个商业产品似乎做了我的想法。我尝试过使用SqlDump,但我的完整服务器名称不适合其SQL Server Name字段。

6 个解决方案

#1

What you need to do is

你需要做的是

Right click on your database in management studio

右键单击管理工作室中的数据库

select tasks -> generate scripts

选择任务 - >生成脚本

Choose table you want data from

选择您想要数据的表格

in next step click advanced and set "types of data to script" set to data only (or whatever you need)

在下一步中单击高级并将“数据类型设置为脚本”设置为仅数据(或任何您需要的)

works with sql serfver 2008 r2. in previous versions of sql server its something like "Script data" which you set to true.

适用于sql serfver 2008 r2。在以前版本的sql server中它的类似“脚本数据”,你设置为true。

#2

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.

tablediff是MS SQL Server附带的免费工具。只要您的源是要从中生成插入的表,并且您有另一个要生成插入的空表,它就会创建您正在寻找的文件。

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.

BCP可能是用于移动数据的实用程序的更好选择 - 但它不会像您正在寻找的那样生成实际的DML插入语句。

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.

有关tablediff实用程序的更多信息,请参阅此链接。

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

请参阅此链接以下载数据库发布向导。

#3

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

我认为这对你有用,但是你必须为每个表运行它。

#4

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.

我知道这不是你想到的,并且建议的工具看起来很有希望,但如果数据从一个SQL Server发送到另一个,那么低开销的替代方案就是使用bcp。以下重要选项包括-E用于保留身份信息,-n用于使用本机格式。执行第一个语句将构建批处理文件以将所有数据转储到文件,第二个语句将创建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 out命令

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

bcp in command

bcp in命令

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

#5

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

这应该是http://sourceforge.net/projects/vwg-ent-man

#6

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.

尝试免费的SSMS工具包,它是SSMS和SSMS Express的加载项。它使您能够为表,dte整个数据库或仅查询结果生成插入脚本。

#1