就我个人而言,我会直接写一个针对该表的 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 ...
一些需要注意的问题:
可以看一下 SSMS Tools Pack 这个 SSMS 的插件,它可以让你完成你需要的操作。
注意!!!按原样使用。在脚本的开头,您可以看到如何使用过程的示例。当然,如果需要,您可以进行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
虽然不完全符合OP的要求,但如果您想为表中的所有记录生成一个insert
脚本,您可以在SSMS(至少2012年版本-可能是旧版本)中完成,无需任何其他附加组件。
右键单击包含数据的表所在的数据库,然后点击 任务 > 生成脚本。您将进入向导,如下图所示。
如果显示了此第一个屏幕,请直接点击下一步。
选择要生成脚本的表。
选择脚本输出方式。
我喜欢“新查询窗口”选项。
点击高级按钮,并为要脚本化的数据类型选择仅数据
您可能想查看选项,看看是否有其他需要修改的内容。完成后点击确定。
一直点击下一步,直到出现此屏幕。一旦所有内容变为绿色,您将获得insert
脚本!
我喜欢在测试脚本时保留此屏幕,以查看是否需要进行任何调整。
我知道这不完全是你想要的,但你可以通过选择语句进行插入:
INSERT INTO tbl(a,b) SELECT c,d FROM tbl2 WHERE c IN(...)
显然这很简略,但我希望它能够表达我的意思。
Squirrel SQL也可以实现这个功能。
编写一个SQL查询,执行测试。然后将其高亮显示,并选择Script/Insert语句(不记得确切的措辞了)。
WinSQL在www.synametrics.com上具有该功能,非常方便。不确定免费版本中是否包含该功能-但您可以获取专业版并尝试30天。
它是一款非常方便易用的查询工具,适用于任何ODBC连接的数据库。
TOAD可以通过数据网格的“另存为”菜单来完成此操作。
我为这个问题编写了一个脚本,它应该适用于任何表格。(我说“应该”是因为脚本没有经过100%的测试,还有一些不完善的地方。)您可以在http://www.jessemclain.com/downloads/code/sql/spd_Tool_Get_Insert_Into_Values.sql.txt找到它。
文件底部有一些垃圾信息,是托管公司添加的,请将其删除。要运行,只需更改@Source_Table的值为您的表格名称即可。
注意:我发布的文本文件在Firefox 3.0.11中可以正常显示,但在IE7中无法正常显示。