Created
June 11, 2019 12:42
-
-
Save dhmacher/9b288633eacbce985905f6849967fb69 to your computer and use it in GitHub Desktop.
Script table contents as INSERT statements
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @object_id int=OBJECT_ID('schema_name.table_name'); | |
DECLARE @name nvarchar(max)=(SELECT QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) FROM sys.objects WHERE [object_id]=@object_id), | |
@cols nvarchar(max)=N'', | |
@query nvarchar(max)=N'', | |
@pagesize varchar(10)=N'100'; | |
WITH cte AS ( | |
SELECT c.column_id, c.[name], t.[name] AS [type] | |
FROM sys.columns AS c | |
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id | |
WHERE c.[object_id]=@object_id | |
AND c.is_computed=0 | |
AND c.column_id NOT IN (SELECT column_id FROM sys.identity_columns WHERE [object_id]=@object_id)) | |
SELECT @cols=@cols+', '+QUOTENAME([name]), | |
@query=@query+'+'', ''+'+(CASE WHEN [type] LIKE '%int' OR [type]='bit' THEN 'ISNULL(CAST('+QUOTENAME([name])+' AS nvarchar(max)), N''NULL'')' | |
WHEN [type] IN ('numeric', 'decimal', 'float', 'real', 'money', 'smallmoney') THEN 'ISNULL(CAST('+QUOTENAME([name])+' AS nvarchar(max)), N''NULL'')' | |
WHEN [type]='date' THEN 'ISNULL(N''{d ''''''+CONVERT(nvarchar(10), '+QUOTENAME([name])+', 121)+N''''''}'', N''NULL'')' | |
WHEN [type] LIKE 'datetime%' THEN 'ISNULL(N''{ts ''''''+CONVERT(nvarchar(20), '+QUOTENAME([name])+', 121)+N''''''}'', N''NULL'')' | |
ELSE 'ISNULL(N''''''''+REPLACE('+QUOTENAME([name])+', N'''', N'''''''')+N'''''''', N''NULL'')' END) | |
FROM cte | |
ORDER BY column_id; | |
SELECT @cols=SUBSTRING(@cols, 3, LEN(@cols)), | |
@query=' | |
SELECT (CASE WHEN rn%'+@pagesize+'=1 THEN '' | |
INSERT INTO '+@name+' ('+@cols+') | |
VALUES '' ELSE '''' END)+ | |
'' (''+s+'')''+ | |
(CASE WHEN LEAD(rn%'+@pagesize+', 1, 1) OVER (ORDER BY rn)=1 THEN '';'' ELSE '','' END) AS [sql] | |
FROM ( | |
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, | |
'+SUBSTRING(@query, 7, LEN(@query))+' AS s | |
FROM '+@name+N' | |
) AS x'; | |
--PRINT @query; | |
EXECUTE sys.sp_executesql @query; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment