从 SQL Server 表生成 INSERT 语句 [英] Generate INSERT statements from a SQL Server Table

问题描述

我有一个包含 330 万条记录的表，并且不想将整个内容从 dev 复制到 prod(在客户端控制的机器上并且无法让链接服务器正常工作).

I have a table of 3.3 million records and don't want to copy the entire thing from dev to prod (on a client controlled machine and can't get the linked server working correctly).

我只想复制 300 左右的这些记录.如何生成 300 条插入语句?

I only want to copy 300 or so of these records. How do I generate the 300 insert statements?

我想要插入的选择 SQL 是:

My select SQL that I want the inserts for is:

select * from data where ID > 9000;

我想要一个可以打印出所有 INSERTS 的查询，以便我可以在生产设备上复制和运行它.

I want a query that will print out all the INSERTS so that I can copy and run it on the production box.

推荐答案

我看到你标记了你的帖子 SQL-Server-2005，这太糟糕了，因为 2008 版有一个向导工具.

I see you tagged your post SQL-Server-2005, that's too bad because version 2008 has a wizard tool for that.

您可以使用连接的字符串构建插入语句.

You could build the insert statements out of concatenated strings.

如果 field1 是一个字符串，field2 是一个数字:

If field1 is a string, field2 a numeric:

select 'insert into data (field1, field2) values('' || field1 || '', ' || char(field2) ||');' from data where ID < 9000;

显然，如果您有很多列，考虑到字符串需要引号，这会很耗时.您可能也必须使用 char() 转换数字列.

Obviously that can be time-consuming if you have lots columns, considering that the strings needs quotes. You may have to convert the numeric columns using char() too.

那应该会给你一个插入语句的列表，像这样:

That should give you a list of insert statements, like this:

insert into data (field1, field2) values('A', 10);
insert into data (field1, field2) values('B', 20);
insert into data (field1, field2) values('C', 30);

也许这不是最优雅的方式，但它确实有效.

Maybe that's not the most elegant way to do this, but it works.

这篇关于从 SQL Server 表生成 INSERT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！