Posts Convert a SQL SELECT into an INSERT Script
Post
Cancel

Convert a SQL SELECT into an INSERT Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DECLARE 
     @includePK BIT = 1,
     @table VARCHAR(MAX) = 'LogEntry',
     @dataFilter VARCHAR(MAX) = 'WHERE date = ''2020-03-10'' '

DECLARE 
     @columnNames VARCHAR(MAX) = '',
     @getDataColumnScript VARCHAR(MAX),
     @queryToGenerateScript VARCHAR(MAX)

-- Get a list of all colmuns
SELECT @columnNames = STUFF
(
    (
     SELECT ',['+ NAME +']' FROM sys.all_columns 
     WHERE OBJECT_ID = OBJECT_ID(@table)
     AND (is_identity != 1 OR @includePK = 1)
     FOR XML PATH('')
    ),
     1,
     1,
     ''
)

-- Create a the column part of the select using the column names
SELECT @getDataColumnScript = STUFF
(
    (
     SELECT ' ISNULL(QUOTENAME(' + NAME + ',' + QUOTENAME('''','''''') + '),' + '''NULL''' + ')+'',''' + '+' FROM sys.all_columns 
     WHERE OBJECT_ID = OBJECT_ID(@table)
     AND (is_identity != 1 OR @includePK = 1)
     FOR XML PATH('')
    ),
     1,
     1,
     ''
)

SELECT @queryToGenerateScript = 'SELECT ''' +
     'INSERT INTO ' + @table + '(' + @columnNames + ')' + 
     'VALUES(''' + '+' + SUBSTRING(@getDataColumnScript, 1, LEN(@getDataColumnScript) -5) + '+' + ''')''' + ' OutputScript ' +
     'FROM ' + @table + ' ' + @dataFilter

EXECUTE (@queryToGenerateScript)

https://www.stevefenton.co.uk/2020/04/convert-a-sql-select-into-an-insert-script/

origin - https://www.pipiscrew.com/?p=17952 convert-a-sql-select-into-an-insert-script

This post is licensed under CC BY 4.0 by the author.
Contents

Trending Tags