Foros del web, para los que viven de bits

Un Saludo Masters, la pregunta es la siguiente :

Estoy en SQL Server 2000. Tengo datos cargados y quiero esportarlos a un archivo .sql, que solo contenga los INSERT INTO y eso.

Como lo Hgo ? Muchas Gracias de Antemano.

Tendrias que crearte un script genere los insert into. No me parece dificil, incluso pienso que puedes hallarlo en google.

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