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