[Resolvido] Gerando scripts de insert automaticamente

[eriva_br 7]

artigo que mostra script para gerar comandos inserts automaticamente de uma tabela

 

CREATE PROC InsertGenerator(@tableName varchar(100)) as--Declare a cursor to retrieve column specific information--for the specified tableDECLARE cursCol CURSOR FAST_FORWARD FORSELECT column_name,data_type FROM information_schema.columns	WHERE table_name = @tableNameOPEN cursColDECLARE @string nvarchar(3000) --for storing the first half							   --of INSERT statementDECLARE @stringData nvarchar(3000) --for storing the data								   --(VALUES) related statementDECLARE @dataType nvarchar(1000) --data types returned								 --for respective columnsSET @string='INSERT '+@tableName+'('SET @stringData='' DECLARE @colName nvarchar(50) FETCH NEXT FROM cursCol INTO @colName,@dataType IF @@fetch_status<>0	begin	print 'Table '+@tableName+' not found, processing skipped.'	close curscol	deallocate curscol	returnEND WHILE @@FETCH_STATUS=0BEGINIF @dataType in ('varchar','char','nchar','nvarchar')BEGIN	SET @stringData=@stringData+'''''''''+			isnull('+@colName+','''')+'''''',''+'ENDELSEif @dataType in ('text','ntext') --if the datatype								 --is text or something elseBEGIN	SET @stringData=@stringData+'''''''''+		  isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'ENDELSEIF @dataType = 'money' --because money doesn't get converted					   --from varchar implicitlyBEGIN	SET @stringData=@stringData+'''convert(money,''''''+		isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'ENDELSEIF @dataType='datetime'BEGIN	SET @stringData=@stringData+'''convert(datetime,''''''+		isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'ENDELSEIF @dataType='image'BEGIN	SET @stringData=@stringData+'''''''''+	   isnull(cast(convert(varbinary,'+@colName+')	   as varchar(6)),''0'')+'''''',''+'ENDELSE--presuming the data type is int,bit,numeric,decimalBEGIN	SET @stringData=@stringData+'''''''''+		  isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'END SET @string=@string+@colName+',' FETCH NEXT FROM cursCol INTO @colName,@dataTypeENDDECLARE @Query nvarchar(4000) -- provide for the whole query,							  -- you may increase the size SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')	VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''	FROM '+@tableNameexec sp_executesql @query --load and run the built queryCLOSE cursColDEALLOCATE cursCol

para chamar a proc:

USE pubsGOInsertGenerator employeeGO

 

 

Author: Sumit Amar

http://www.ecodebank.com/details/?catid=16...id=0&nid=96