MSSQL: Export Data From Table

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 45 46 47 48 49 50 51 52 53 54

CREATE PROCEDURE [dbo].[sp_export_table] ( @TableName VARCHAR(100) ) AS BEGIN SET NOCOUNT ON; --DECLARE @TableName VARCHAR(100) = 't_users'; DECLARE @string VARCHAR(MAX); DECLARE @fields VARCHAR(MAX); SET @fields = (SELECT STUFF((SELECT ',' + CONVERT(VARCHAR(MAX),COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION ASC FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')); SET @string = 'INSERT INTO ' + @TableName + ' (' + @fields + ') VALUES ('; DECLARE @query VARCHAR(MAX) = 'SELECT ('; DECLARE @p INT = 0; WHILE @p > -1 BEGIN DECLARE @c VARCHAR(100) = NULL; DECLARE @n VARCHAR(3) = NULL; DECLARE @t VARCHAR(100) = NULL; SELECT @c = COLUMN_NAME, @n = IS_NULLABLE, @t = DATA_TYPE, @p = ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @p + 1; IF @c IS NOT NULL BEGIN DECLARE @quote VARCHAR(100) = (CASE WHEN @t LIKE '%char%' OR @t LIKE '%date%' OR @t LIKE '%text%' THEN '''''''''' ELSE '''''' END); SET @query = @query + 'REPLACE(REPLACE(ISNULL(' + @quote + ' + REPLACE(CONVERT(VARCHAR(MAX),' + @c + '),'''''''','''''''''''') + ' + @quote + ', ''NULL''),CHAR(13),'''''' + CHAR(13) + ''''''),CHAR(10),'''''' + CHAR(10) + '''''') + '','' + '; END IF @c IS NULL BEGIN SET @p = -1; END END SET @query = LEFT(@query, LEN(@query) - 8) + ') AS f_result FROM ' + @TableName; DECLARE @Output TABLE(f_result VARCHAR(MAX)); INSERT INTO @Output EXEC(@query) SELECT 'TRUNCATE TABLE ' + @TableName + ';' UNION ALL SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON;' AS f_result UNION ALL SELECT (@string + f_result + ');') FROM @Output UNION ALL SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF;' END