in Programming

T-SQL Export Table Data

Here’s a quick script to export table data:

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'