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.
| ||||
Re: Generar Script de Datos en SQL Server 2000 Tendrias que crearte un script genere los insert into. No me parece dificil, incluso pienso que puedes hallarlo en google.
__________________ No repitamos temas, usemos el Motor de busquedas Plantea bien tu problema: Ayúdanos a ayudarte. |
| ||||
Re: Generar Script de Datos en SQL Server 2000 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 |