Created
September 4, 2018 12:27
-
-
Save tvaidyan/720bedf2de40c7498f07153fcb7b5397 to your computer and use it in GitHub Desktop.
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
-- Code to generate INSERT statements from query results. Got this from: | |
-- https://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script-sql-server | |
CREATE PROCEDURE sp_generate_insertscripts | |
( | |
@TABLENAME VARCHAR(MAX), | |
@FILTER_CONDITION VARCHAR(MAX)='' | |
) | |
AS | |
BEGIN | |
SET NOCOUNT ON | |
DECLARE @TABLE_NAME VARCHAR(MAX), | |
@CSV_COLUMN VARCHAR(MAX), | |
@QUOTED_DATA VARCHAR(MAX), | |
@TEXT VARCHAR(MAX), | |
@FILTER VARCHAR(MAX) | |
SET @TABLE_NAME=@TABLENAME | |
SELECT @FILTER=@FILTER_CONDITION | |
SELECT @CSV_COLUMN=STUFF | |
( | |
( | |
SELECT ',['+ NAME +']' FROM sys.all_columns | |
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND | |
is_identity!=1 FOR XML PATH('') | |
),1,1,'' | |
) | |
SELECT @QUOTED_DATA=STUFF | |
( | |
( | |
SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns | |
WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND | |
is_identity!=1 FOR XML PATH('') | |
),1,1,'' | |
) | |
SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER | |
--SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT | |
EXECUTE (@TEXT) | |
SET NOCOUNT OFF | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment