DECLARE
@TableName SYSNAME
, @ObjectID INT
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
'[' + s.name + '].[' + t.name + ']'
, t.[object_id]
FROM (
SELECT DISTINCT
t.[schema_id]
, t.[object_id]
, t.name
FROM sys.objects t WITH (NOWAIT)
JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id]
WHERE p.[rows] > 0
AND t.[type] = 'U'
) t
JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id]
WHERE t.name IN ('<your table name>')
OPEN [tables]
FETCH NEXT FROM [tables] INTO
@TableName
, @ObjectID
DECLARE
@SQLInsert NVARCHAR(MAX)
, @SQLColumns NVARCHAR(MAX)
, @SQLTinyColumns NVARCHAR(MAX)
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@SQLInsert = ''
, @SQLColumns = ''
, @SQLTinyColumns = ''
;WITH cols AS
(
SELECT
c.name
, datetype = t.name
, c.column_id
FROM sys.columns c WITH (NOWAIT)
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
AND t.name NOT IN ('xml', 'geography', 'geometry', 'hierarchyid')
)
SELECT
@SQLTinyColumns = STUFF((
SELECT ', [' + c.name + ']'
FROM cols c
ORDER BY c.column_id
FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
, @SQLColumns = STUFF((SELECT CHAR(13) +
CASE
WHEN c.datetype = 'uniqueidentifier'
THEN ' + '';'' + ISNULL('''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''', ''NULL'')'
WHEN c.datetype IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary')
THEN ' + '';'' + ISNULL('''' + CAST(REPLACE([' + c.name + '], '''', '''''''') AS NVARCHAR(MAX)) + '''', ''NULL'')'
WHEN c.datetype = 'datetime'
THEN ' + '';'' + ISNULL('''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''', ''NULL'')'
ELSE
' + '';'' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), ''NULL'')'
END
FROM cols c
ORDER BY c.column_id
FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 10, 'CHAR(13) + '''' +')
DECLARE @SQL NVARCHAR(MAX) = '
SET NOCOUNT ON;
DECLARE
@SQL NVARCHAR(MAX) = ''''
, @x INT = 1
, @count INT = (SELECT COUNT(1) FROM ' + @TableName + ')
IF EXISTS(
SELECT 1
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(''tempdb..#import'')
)
DROP TABLE #import;
SELECT ' + @SQLTinyColumns + ', ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + ')
INTO #import
FROM ' + @TableName + '
WHILE @x < @count BEGIN
SELECT @SQL = STUFF((
SELECT ' + @SQLColumns + ' + ''''' + '
FROM #import
WHERE RowNumber BETWEEN @x AND @x + 9
FOR XML PATH, TYPE, ROOT).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')
PRINT(@SQL)
SELECT @x = @x + 10
END'
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM [tables] INTO
@TableName
, @ObjectID
END
CLOSE [tables]
DEALLOCATE [tables]
2 回答
此脚本将指定表中的行导出到任何表结构的INSERT语句 . 所以,你只需要复制结果并在SSMS的sql文件中运行它 -
在输出中你得到这样的东西(AdventureWorks.Person.Address):
UPDATE:
对于任何表结构,此脚本在输出窗口中将指定表中的行导出为CSV格式 .
在输出中你得到这样的东西(AdventureWorks.Person.Person):
尝试使用
bcp
命令行实用程序,它非常有效地处理大型数据集的导入/导出:-T表示受信任的身份验证 . -n表示原生格式,因此您不必担心数据类型,逗号等 . 但是,这确实意味着您无法在编辑器中查看数据;它只能用于加载回SQL Server . 如果您想要CSV格式,可以使用-c .
要导入: