我希望能够直接将SQL Server中的自定义选择查询结果集导出为插入语句。希望在SSMS中右键单击时，能够看到“插入”选项，与当前提供的csv、txt等选项一起使用。由于我没有从现有物理表进行导出的权限，也无法创建新表，因此对于我来说，脚本化物理表的选项不可用。我必须从临时表或查询窗口中的结果集进行脚本处理。目前我可以导出csv文件，然后再将其导入到另一个表中，但这样做会浪费很多时间。该工具必须能够创建适当的插入语句，并在创建NULL值的数据类型时识别它们。
从SQL导出结果集到插入语句的工具？
16
- Abdu
3
如果您没有从现有表中导出数据，那么您怎么能期望将一个选择语句转换为插入语句呢？ - Eric
是的，你是在假设只能从具有相同模式的表格中插入吗？我不明白这有什么难度。 - dkretz
1当我执行一个select语句时，我会得到一个结果集。我想将这个结果集导出为一堆insert语句，以便在其他地方重新创建相同的结果集。忘记任何现有的表格。 - Abdu
9个回答
15
就我个人而言，我会直接写一个针对该表的 select 语句，并自己生成插入语句。非常简单。
例如：
SELECT 'insert into [pubs].[dbo].[authors](
[au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract])
values( ''' +
[au_id] + ''', ''' +
[au_lname] + ''', ''' +
[au_fname] + ''', ''' +
[phone] + ''', ''' +
[address] + ''', ''' +
[city] + ''', ''' +
[state] + ''', ''' +
[zip] + ''', ' +
cast([contract] as nvarchar) + ');'
FROM [pubs].[dbo].[authors]
会产生
insert into [pubs].[dbo].[authors](
[au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract])
values( '172-32-1176', 'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1);
insert into [pubs].[dbo].[authors](
[au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract])
values( '213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);
... etc ...
一些需要注意的问题：
- 不要忘记使用单引号将内容包起来。
- 这种方式假定数据库是干净的，不能防止SQL注入攻击。
- John MacIntyre
1
谢谢，但这比导出到文件再导入更麻烦，特别是在编写复杂的选择语句时更麻烦。而且还要担心太多单引号！ :) - Abdu
11
可以看一下 SSMS Tools Pack 这个 SSMS 的插件，它可以让你完成你需要的操作。
- Mladen Prajdic
6
这是从现有表创建的。我需要从查询窗口中的结果集创建。 - Abdu
它也可以做到这一点。右键单击结果集，选择“脚本网格结果”。 - Mladen Prajdic
请注意，@MladenPrajdic是程序作者（感谢！），SSMS Tools Pack从2012-09-18发布的2.5.0.0版本开始成为商业产品。有一个30天的演示版本可用，以及仍然免费的旧版本。 - Joel Purra
3不太准确。:) SSMS Tools Pack仅适用于SSMS 2012的商业版本。SSMS 2008和2005的用户仍可免费使用SSMS Tools Pack。 - Mladen Prajdic
非常老的帖子，但这仍然有效吗？似乎我只能将其脚本化为SELECT，并且无法将其保存为文件。 - mathkid91
是的，它可以。如果您遇到问题，请通过网站发送电子邮件给我。 - Mladen Prajdic
4
注意！！！按原样使用。在脚本的开头，您可以看到如何使用过程的示例。当然，如果需要，您可以进行INSERT表达式或添加所需转换的数据类型。
脚本的结果是带有UNION ALL的连接SELECT表达式。
请注意您数据库的排序规则。我没有测试比我需要的其他排序规则。
对于较长的字段，我建议在结果网格中使用[保存结果为...]而不是复制。因为您可能会得到被截断的脚本。
/*
USE AdventureWorks2012
GO
IF OBJECT_ID('tempdb..#PersonTbl') IS NOT NULL
DROP TABLE #PersonTbl;
GO
SELECT TOP (100)
BusinessEntityID
, PersonType
, NameStyle
, Title
, FirstName
, MiddleName
, LastName
, Suffix
, EmailPromotion
, CONVERT(NVARCHAR(MAX), AdditionalContactInfo) AS [AdditionalContactInfo]
, CONVERT(NVARCHAR(MAX), Demographics) AS [Demographics]
, rowguid
, ModifiedDate
INTO #PersonTbl
FROM Person.Person
EXEC dbo.p_GetTableAsSqlText
@table_name = N'#PersonTbl'
EXEC dbo.p_GetTableAsSqlText
@table_name = N'Person'
, @table_owner = N'Person'
*/
/*********************************************************************************************/
IF OBJECT_ID('dbo.p_GetTableAsSqlText', 'P') IS NOT NULL
DROP PROCEDURE dbo.p_GetTableAsSqlText
GO
CREATE PROCEDURE [dbo].[p_GetTableAsSqlText]
@table_name NVARCHAR(384) /*= 'Person'|'#Person'*/
, @database_name NVARCHAR(384) = NULL /*= 'AdventureWorks2012'*/
, @table_owner NVARCHAR(384) = NULL /*= 'Person'|'dbo'*/
/*WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name'*/
AS /*OLEKSANDR PAVLENKO p_GetTableAsSqlText ver.2016.10.11.1*/
DECLARE @isTemporaryTable BIT = 0
/*[DATABASE NAME]*/
IF (PATINDEX('#%', @table_name) <> 0)
BEGIN
SELECT @database_name = DB_NAME(2) /*2 - 'tempdb'*/
, @isTemporaryTable = 1
END
ELSE
SET @database_name = COALESCE(@database_name, DB_NAME())
/*END [DATABASE NAME]*/
/*[SCHEMA]*/
SET @table_owner = COALESCE(@table_owner, SCHEMA_NAME())
DECLARE @database_nameQuoted NVARCHAR(384) = QUOTENAME(@database_name, '')
DECLARE @table_ownerQuoted NVARCHAR(384) = QUOTENAME(@table_owner, '')
DECLARE @table_nameQuoted NVARCHAR(384) = QUOTENAME(@table_name, '')
DECLARE @full_table_name NVARCHAR(769)
/*384 + 1 + 384*/
DECLARE @table_id INT
SET @full_table_name = CONCAT(@database_nameQuoted, '.', @table_ownerQuoted, '.', @table_nameQuoted)
SET @table_id = OBJECT_ID(@full_table_name)
CREATE TABLE #ColumnTbl
(
ColumnId INT
, ColName sysname COLLATE DATABASE_DEFAULT
, TypeId TINYINT
, TypeName sysname COLLATE DATABASE_DEFAULT
, TypeMaxLength INT
)
DECLARE @dynSql NVARCHAR(MAX) = CONCAT('
INSERT INTO #ColumnTbl
SELECT ISC.ORDINAL_POSITION AS [ColumnId]
, ISC.COLUMN_NAME AS [ColName]
, T.system_type_id AS [TypeId]
, ISC.DATA_TYPE AS [TypeName]
, ISC.CHARACTER_MAXIMUM_LENGTH AS [TypeMaxLength]
FROM ', @database_name, '.INFORMATION_SCHEMA.COLUMNS AS [ISC]
INNER JOIN ', @database_name, '.sys.objects AS [O] ON ISC.TABLE_NAME = O.name
INNER JOIN ', @database_name, '.sys.types AS [T] ON ISC.DATA_TYPE = T.name
WHERE ISC.TABLE_CATALOG = "', @database_name, '"
AND ISC.TABLE_SCHEMA = "', @table_owner, '"
AND O.object_id = ', @table_id)
IF (@isTemporaryTable = 0)
SET @dynSql = CONCAT(@dynSql, '
AND ISC.TABLE_NAME = "', @table_name, '"
')
ELSE
SET @dynSql = CONCAT(@dynSql, '
AND ISC.TABLE_NAME LIKE "', @table_name, '%"
')
SET @dynSql = REPLACE(@dynSql, '"', '''')
EXEC(@dynSql)
DECLARE @columnNamesSeparated NVARCHAR(MAX) = SUBSTRING((SELECT ', [' + C.ColName + ']' AS [text()]
FROM #ColumnTbl AS [C]
ORDER BY C.ColumnId
FOR
XML PATH('')
), 2, 4000)
--SELECT @columnNamesSeparated
DECLARE @columnNamesSeparatedWithTypes NVARCHAR(MAX) = SUBSTRING((SELECT '+", " + "CONVERT(' + (CASE C.TypeId
WHEN 231 /*NVARCHAR*/
THEN CONCAT(C.TypeName, '(',
(CASE WHEN C.TypeMaxLength = -1 THEN 'MAX'
ELSE CONVERT(NVARCHAR(MAX), C.TypeMaxLength)
END), ')')
WHEN 239 /*NCHAR*/
THEN CONCAT(C.TypeName, '(', C.TypeMaxLength, ')')
/*WHEN -1 /*XML*/ THEN '(MAX)'*/
ELSE C.TypeName
END) + ', "+ COALESCE('
+ (CASE C.TypeId
WHEN 56 /*INT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
WHEN 40 /*DATE*/
THEN 'N"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + '], 101) + """"'
WHEN 60 /*MONEY*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
WHEN 61 /*DATETIME*/
THEN '"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + '], 21) + """"'
WHEN 104 /*BIT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
WHEN 106 /*DECIMAL*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
WHEN 127 /*BIGINT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
WHEN 189 /*TIMESTAMP*/
THEN 'N"""" + CONVERT(NVARCHAR(MAX), SUBSTRING([' + C.ColName
+ '], 1, 8000), 1) + """"'
WHEN 241 /*XML*/
THEN '"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + ']) + """"'
ELSE 'N"""" + CONVERT(NVARCHAR(MAX), REPLACE([' + C.ColName
+ '], """", """""")) + """"'
END) + ' , "NULL") + ") AS [' + C.ColName + ']"' + CHAR(10) COLLATE DATABASE_DEFAULT AS [text()]
FROM #ColumnTbl AS [C]
ORDER BY C.ColumnId
FOR
XML PATH('')
), 9, 100000)
/*SELECT @columnNamesSeparated, @full_table_name*/
DECLARE @dynSqlText NVARCHAR(MAX) = CONCAT(N'
SELECT (CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) = 1 THEN "
/*INSERT INTO ', @full_table_name, '
(', @columnNamesSeparated, '
)*/', '
SELECT T.* /*INTO #ResultTbl*/
FROM (
"
ELSE "UNION ALL "
END) + "SELECT "+ ', @columnNamesSeparatedWithTypes, ' FROM ', @full_table_name)
SET @dynSqlText = CONCAT(@dynSqlText, ' UNION ALL SELECT ") AS [T]
/*SELECT *
FROM #ResultTbl*/
"')
SET @dynSqlText = REPLACE(@dynSqlText, '"', '''')
--SELECT @dynSqlText AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B]
EXEC(@dynSqlText)
IF OBJECT_ID('tempdb..#ColumnTbl') IS NOT NULL
DROP TABLE #ColumnTbl;
GO
- FoxMalder133
3
虽然不完全符合OP的要求，但如果您想为表中的所有记录生成一个insert脚本，您可以在SSMS（至少2012年版本-可能是旧版本）中完成，无需任何其他附加组件。
右键单击包含数据的表所在的数据库，然后点击 任务 > 生成脚本。您将进入向导，如下图所示。
如果显示了此第一个屏幕，请直接点击下一步。
选择要生成脚本的表。
选择脚本输出方式。
我喜欢“新查询窗口”选项。
点击高级按钮，并为要脚本化的数据类型选择仅数据
您可能想查看选项，看看是否有其他需要修改的内容。完成后点击确定。
一直点击下一步，直到出现此屏幕。一旦所有内容变为绿色，您将获得insert脚本！
我喜欢在测试脚本时保留此屏幕，以查看是否需要进行任何调整。
- Grinn
1
这将提供所有数据。在我的情况下，这将导致某个表的2 GB数据。我想为测试数据库创建一个较小的测试集。 - Roel
2
我知道这不完全是你想要的，但你可以通过选择语句进行插入：
INSERT INTO tbl(a,b) SELECT c,d FROM tbl2 WHERE c IN(...)
显然这很简略，但我希望它能够表达我的意思。
- Ian Jacobs
2
我想要准备好的可执行插入语句，这样我就可以将它们复制到其他地方进行执行。 - Abdu
此外，如果您说tbl是一个新表，我就无法创建新表。我认为我的问题已经很清楚了。 - Abdu
2
Squirrel SQL也可以实现这个功能。
编写一个SQL查询，执行测试。然后将其高亮显示，并选择Script/Insert语句（不记得确切的措辞了）。
- sleske
1
1在SQuirrel SQL Client Version 3.2.1中，菜单项路径为“Session/Scripts/Create Data Script from SQL”。我刚刚通过这个评论重新发现了它。感谢Stack Overflow！ - chaotic3quilibrium
1
WinSQL在www.synametrics.com上具有该功能，非常方便。不确定免费版本中是否包含该功能-但您可以获取专业版并尝试30天。
它是一款非常方便易用的查询工具，适用于任何ODBC连接的数据库。
- Ron Savage
1
WinSQL可以完成这个任务。很遗憾它只在Pro版本中可用，价格为$249。 - Abdu
1
TOAD可以通过数据网格的“另存为”菜单来完成此操作。
- Noah Yetter
2
在免费版中看不到这个功能。我看到一个导出向导和一个“SQL脚本”输出格式，然后它说此格式没有许可证。我猜想这意味着我需要购买商业版本。但我不愿意支付$595（他们最便宜的版本）来获得这个功能。 - Abdu
此功能可能不适用于所有版本和/或平台，我正在使用Oracle的9.7版本。在我看来，TOAD很容易就能抵得上门票的价格，尽管预算和价值标准显然有所不同。 - Noah Yetter
0
我为这个问题编写了一个脚本，它应该适用于任何表格。（我说“应该”是因为脚本没有经过100%的测试，还有一些不完善的地方。）您可以在http://www.jessemclain.com/downloads/code/sql/spd_Tool_Get_Insert_Into_Values.sql.txt找到它。
文件底部有一些垃圾信息，是托管公司添加的，请将其删除。要运行，只需更改@Source_Table的值为您的表格名称即可。
注意：我发布的文本文件在Firefox 3.0.11中可以正常显示，但在IE7中无法正常显示。
- Jesse
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接