in Programming |

T-SQL Export Table Data

Here’s a quick script to export table data:

?
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
if exists ( select * from sysobjects where name = 'ExportData_P' )
    drop proc ExportData_P
go
 
CREATE PROC dbo.ExportData_P (
    @tableName varchar(500),
    @where varchar(5000) = '(1=1)'
)
AS
BEGIN
    SET NOCOUNT ON
 
DECLARE @sql varchar(8000)
DECLARE @fieldList varchar(8000)
DECLARE @valueList varchar(8000)
SELECT @fieldList = '', @valueList = ''
 
DECLARE @cols TABLE ( column_name nvarchar(250), data_type varchar(250) )
DECLARE @c nvarchar(250), @data_type varchar(250)
 
INSERT INTO @cols
select column_name, data_type
from information_Schema.columns
where table_name = @tableName
 
WHILE EXISTS ( SELECT TOP 1 * FROM @cols )
BEGIN
    SELECT TOP 1 @c = column_name, @data_type = data_type FROM @cols
 
    SELECT
    @fieldList = @fieldList + @c + ', ',
    @valueList = @valueList + CHAR(13) + 'case when ' + @c + ' is null then ''NULL'' else '''''''' + ' +
        case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
            ' REPLACE ( REPLACE ( REPLACE ( '
            else ''
        end +
        'IsNull ( convert(varchar' +
        ( -- change this section to pass the length of varchar to convert
            case when @data_type in ( 'uniqueidentifier' ) then '(50)'
                when @data_type in ( 'text', 'ntext' ) then '(8000)'
            else '' end
        ) +
        ', ' +
        @c +
        '), '''' )' + -- end is null
        case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
            ', CHAR(39), CHAR(39)+CHAR(39) ), CHAR(13), '''' + CHAR(13) + ''''), CHAR(9), '''' + CHAR(9) + '''') '
            else ''
        end +
        ' + '''''''' end + '', '' + '
 
    DELETE FROM @cols WHERE column_name = @c
END
 
SELECT @fieldList = LEFT ( @fieldList, LEN(@fieldList)-1 ),
    @valueList = LEFT ( @valueList, LEN(@valueList)-1 )
 
SELECT @sql = 'select ''insert into ' + @tableName + ' (' + @fieldList + ') ' +
    ' VALUES ( ''+ ' + left ( @valueList, len(@valueList)-5) + ''') '' from ' + @tableName +
    ' WHERE ' + @where
 
-- into [#mcoe_temp_export' + @tableName + ']  
print @sql
EXEC ( @sql )
--EXEC ( 'select * from [#mcoe_temp_export' + @tableName + ']' )       
 
SET NOCOUNT OFF
 
 
END
 
go
 
exec dbo.ExportData_P 'tablename'