SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Xform into INSERT INTO (MSSQL)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Xform into INSERT INTO (MSSQL)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Xform into INSERT INTO (MSSQL) Reply with quote
Code:

$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)
$$

Sat Sep 05, 2015 3:28 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.