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'
