gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
Xform into INSERT INTO (MSSQL) |
|
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
$PROMPT(@schema_name,Schema of the table to insert into?,dbo,Xform to INSERT INTO)$
$PROMPT(@table_name,Name of the table to insert into?,,Xform to INSERT INTO)$
$PROMPT(@fill_method,Method to use for inserts?,,Xform to INSERT INTO,MULTIVALUES SINGLEVALUES UNIONS)$
$$
SET ARITHABORT ON
DECLARE @header NVARCHAR(MAX) = ''
,@column_list NVARCHAR(MAX) = ''
,@select_column_list NVARCHAR(MAX) = ''
,@fill_method NVARCHAR(MAX) = '$@fill_method$'
,@wholeshebang NVARCHAR(MAX) = ''
,@id_object INT
,@temp_table_name NVARCHAR(256) = '##export_to_insert' + CAST(@@SPID AS NVARCHAR(8))
,@schema_name NVARCHAR(256) = CASE WHEN SUBSTRING('$@table_name$', 1, 1) = '@' THEN '' ELSE 'dbo.' END
,@table_name NVARCHAR(256) = '$@table_name$'
,@stmt NVARCHAR(MAX)
DECLARE @table_structure TABLE ([name] SYSNAME ,column_id INT ,[type] SYSNAME ,column_prefix SYSNAME ,column_suffix SYSNAME)
IF OBJECT_ID('tempdb..' + @temp_table_name) IS NOT NULL
EXEC ('DROP TABLE ' + @temp_table_name)
SET @wholeshebang = '$CURRENT(selection, escape_quotes)$'
/*
SET @wholeshebang = 'select 1 as a, ''simachar'' as b, N''harom'' as c, CAST( ''2015-09-03'' as DATETIME) as d, 0x12345678 as vb
union select null as a, ''nemsimachar'' as b, N''negy'' as c, CAST( ''2015-01-03'' as DATETIME) as d, 0x33333678 as vb'
*/
SET @wholeshebang = CASE
WHEN CHARINDEX('--4b' ,@wholeshebang) = 0
AND CHARINDEX('--4e' ,@wholeshebang) = 0 THEN '--4b' + CHAR(10) + @wholeshebang + CHAR(10) + '--4e'
WHEN CHARINDEX('--4b' ,@wholeshebang) = 0 THEN '--4b' + CHAR(10) + @wholeshebang
WHEN CHARINDEX('--4e' ,@wholeshebang) = 0 THEN @wholeshebang + CHAR(10) + '--4e'
ELSE @wholeshebang
END
SELECT
@stmt = REPLACE(REPLACE(@wholeshebang ,'--4b' ,'SELECT * INTO ' + @temp_table_name + ' FROM ( ' + CHAR(10)) ,'--4e' ,') as ganyolas WHERE 1 = 0' + CHAR(10))
EXEC (@stmt)
SET @id_object = OBJECT_ID('tempdb..' + @temp_table_name)
INSERT INTO @table_structure
(
[name]
,column_id
,[type]
,column_prefix
,column_suffix
)
SELECT
CONCAT('[' ,ac.[name] ,']')
,ac.column_id
,TYPE_NAME(ac.system_type_id)
,CASE
WHEN TYPE_NAME(ac.system_type_id) IN ('nvarchar' ,'nchar') THEN '''N'''''' + '
WHEN TYPE_NAME(ac.system_type_id) IN ('char' ,'varchar' ,'date' ,'datetime' ,'datetime2' ,'time') THEN ''''''''' + '
ELSE ''
END
,CASE
WHEN TYPE_NAME(ac.system_type_id) IN ('nvarchar' ,'nchar' ,'char' ,'varchar' ,'date' ,'datetime' ,'datetime2' ,'time') THEN ' + '''''''''
ELSE ''
END
FROM
tempdb.sys.all_columns AS ac
WHERE ac.[object_id] = @id_object
ORDER BY
ac.column_id
SET @column_list = (
SELECT
CONCAT(CASE WHEN ac.column_id = 1 THEN '' ELSE ',' END ,'[' ,ac.[name] ,']')
FROM
tempdb.sys.all_columns AS ac
WHERE ac.[object_id] = @id_object
ORDER BY
ac.column_id
FOR XML PATH(''), TYPE
).value('(./text()[1])' ,'nvarchar(max)')
SELECT
@header = 'INSERT INTO '+ @schema_name + @table_name + '(' + @column_list + ')'
SELECT
@select_column_list = (
SELECT
CONCAT(CASE WHEN ts.column_id = 1 THEN '' ELSE ' + '', '' + ' END , 'ISNULL(' + ts.column_prefix + 'CAST( ' + ts.[name] + ' as NVARCHAR(MAX))' + ts.column_suffix + ', ''NULL'')')
FROM
@table_structure AS ts
ORDER BY
ts.column_id
FOR XML PATH(''), TYPE
).value('(./text()[1])' ,'nvarchar(max)')
SELECT
@select_column_list =
CASE
WHEN @fill_method = 'MULTIVALUES' THEN ' CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL AS a)) % 1000 = 1 THEN '''
+ @header + CHAR(10) + ' VALUES '' + CHAR(10) + '' ('''
+ 'ELSE '', ('' END + '
WHEN @fill_method = 'SINGLEVALUES' THEN '''' + @header + ' VALUES ('' + '
WHEN @fill_method = 'UNIONS' THEN ' CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL AS a)) = 1 THEN '''
+ @header + CHAR(10) + ' SELECT '' ELSE ''UNION ALL SELECT '' END + '
ELSE 'error'
END
+ @select_column_list
+ CASE
WHEN @fill_method IN ('MULTIVALUES') THEN ' + '')'''
WHEN @fill_method IN ('SINGLEVALUES') THEN ' + '');'''
-- WHEN @fill_method = 'SINGLEVALUES' THEN ''
WHEN @fill_method = 'UNIONS' THEN ''
ELSE 'error'
END
--SELECT @select_column_list
SELECT
@stmt = REPLACE(REPLACE(@wholeshebang ,'--4b' ,'SELECT ' + @select_column_list + ' FROM ( ' + CHAR(10)) ,'--4e' ,') as ganyolas WHERE 1 = 1' + CHAR(10))
--SELECT @stmt
exec(@stmt)
IF OBJECT_ID('tempdb..' + @temp_table_name) IS NOT NULL
EXEC ('DROP TABLE ' + @temp_table_name)
$$
|
|
|