Generar Script de Datos en SQL Server 2000

[iislas]

No se si le sirva, este es un store que genera los INSERT's de 1 o varias tablas.

CREATE Procedure dbo.Common_GenInsertStatement
(
@TableName sysname,
@TrimFlag bit = 0,
@Identity_Handle bit = 0
)
as
/*<summary>**************************************** ******
#author : xuchangjiang
#date : 2002-07-26
#desc : Generate "insert" script
#problem: Cannot surpport text column, later i will handle the problem by making a few changes?

#Revision History:
#DATE PROGRAMMER DESCRIPTION
#========== =========== =================================
#2002/07/31 xu.chang-jiang Handle the problem of No splitter
#2002/08/01 xu.chang-jiang Handle the problem of IDENTITY
#2002/08/05 xu.chang-jiang Handle the problem of NULL
#2002/10/17 xu.chang-jiang Handle the problem of NULL at the end of end.

**********************************************</summary>*/

set nocount on

declare @ColumnName sysname
, @ColumnType int
, @ColOrder int
, @TableID int
, @ExecStr varchar(4000)
, @HeadStr varchar(4000)
, @MaxCol int
, @ColList bit

declare @TrimHeader varchar(12)
, @TrimTail varchar(2)
, @colstat smallint
, @identity bit
, @identity_insert_on varchar(100)
, @identity_insert_off varchar(100)

--We should find out whether the result need to be trimmed.
if @TrimFlag = 1
begin
set @TrimHeader = 'LTRIM(RTRIM('
set @TrimTail = '))'
end
else
begin
set @TrimHeader = ''
set @TrimTail = ''
end

set @ColList = 1
set @identity = 0

set @TableID = object_id(@TableName)
set @HeadStr = '('
set @identity_insert_on = 'SET IDENTITY_INSERT ' + @TableName + ' ON'
set @identity_insert_off = 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

select @MaxCol = max(colorder)
from syscolumns
where id = @TableID

declare ColumnList cursor scroll for
select name, xtype, colorder, colstat
from syscolumns
where id = @TableID
order by colorder

open ColumnList

fetch first
from ColumnList
into @ColumnName, @ColumnType, @ColOrder, @colstat

while @@fetch_status <> -1
begin

if @colstat = 1 and @Identity_Handle = 1
set @identity = 1

if not(@colstat = 1 and @Identity_Handle = 0)
begin
set @HeadStr = @HeadStr +
case when @ColOrder < @MaxCol then @ColumnName + ', '
else @ColumnName + ')'
end
end

fetch next
from ColumnList
into @ColumnName, @ColumnType, @ColOrder, @colstat
end

if @ColList = 1
set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' ' + @HeadStr + ' VALUES ('' + '
else
set @ExecStr = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '

fetch first
from ColumnList
into @ColumnName, @ColumnType, @ColOrder, @colstat

while @@fetch_status <> -1
begin

if not(@colstat = 1 and @Identity_Handle = 0)
begin
set @ExecStr = @ExecStr +
case when @ColumnType in (48,52,56,59,62,104,106,108,127) then 'convert(varchar, ' + @ColumnName + ')'
when @ColumnType in (61) then ''''''''' + replace(convert(varchar, ' + @ColumnName + ', 120), ''.'', '''') + '''''''''
else '''N'' + '''''''' + ' +@TrimHeader+'replace(' + @ColumnName + ', '''''''', '''''''''''')'+@TrimTail+' + '''''''''
end +
case when @ColOrder < @MaxCol then ','+ ''','' as Splittor'+', '
else ',' + ''')'' as Tail'+' FROM ' + @TableName
end
end

fetch next
from ColumnList
into @ColumnName, @ColumnType, @ColOrder, @colstat
end

close ColumnList
deallocate ColumnList

if @identity = 1
select @identity_insert_on

exec (@Execstr)

if @identity = 1
select @identity_insert_off



return