A procedure abaixo cria query INSERT a partir dos registros de uma tabela no SQL Server.<\/p>\n
[sql]<\/p>\n
CREATE PROC [dbo].[InsertGenerator]
\n(@tableName varchar(100)) as<\/p>\n
–Declare a cursor to retrieve column specific information
\n–for the specified table
\nDECLARE cursCol CURSOR FAST_FORWARD FOR
\nSELECT column_name,data_type FROM information_schema.columns
\n WHERE table_name = @tableName
\nOPEN cursCol
\nDECLARE @string nvarchar(3000) –for storing the first half
\n –of INSERT statement
\nDECLARE @stringData nvarchar(3000) –for storing the data
\n –(VALUES) related statement
\nDECLARE @dataType nvarchar(1000) –data types returned
\n –for respective columns
\nSET @string=’INSERT ‘+@tableName+'(‘
\nSET @stringData=”<\/p>\n
DECLARE @colName nvarchar(50)<\/p>\n
FETCH NEXT FROM cursCol INTO @colName,@dataType<\/p>\n
IF @@fetch_status<>0
\n begin
\n print ‘Table ‘+@tableName+’ not found, processing skipped.’
\n close curscol
\n deallocate curscol
\n return
\nEND<\/p>\n
WHILE @@FETCH_STATUS=0
\nBEGIN
\nIF @dataType in (‘varchar’,’char’,’nchar’,’nvarchar’)
\nBEGIN
\n SET @stringData=@stringData+””””’+
\n isnull(‘+@colName+’,””)+”””,”+’
\nEND
\nELSE
\nif @dataType in (‘text’,’ntext’) –if the datatype
\n –is text or something else
\nBEGIN
\n SET @stringData=@stringData+””””’+
\n isnull(cast(‘+@colName+’ as varchar(2000)),””)+”””,”+’
\nEND
\nELSE
\nIF @dataType = ‘money’ –because money doesn’t get converted
\n –from varchar implicitly
\nBEGIN
\n SET @stringData=@stringData+”’convert(money,”””+
\n isnull(cast(‘+@colName+’ as varchar(200)),”0.0000”)+”””),”+’
\nEND
\nELSE
\nIF @dataType=’datetime’
\nBEGIN
\n SET @stringData=@stringData+”’convert(datetime,”””+
\n isnull(cast(‘+@colName+’ as varchar(200)),”0”)+”””),”+’
\nEND
\nELSE
\nIF @dataType=’image’
\nBEGIN
\n SET @stringData=@stringData+””””’+
\n isnull(cast(convert(varbinary,’+@colName+’)
\n as varchar(6)),”0”)+”””,”+’
\nEND
\nELSE–presuming the data type is int,bit,numeric,decimal
\nBEGIN
\n SET @stringData=@stringData+””””’+
\n isnull(cast(‘+@colName+’ as varchar(200)),”0”)+”””,”+’
\nEND<\/p>\n
SET @string=@string+@colName+’,’<\/p>\n
FETCH NEXT FROM cursCol INTO @colName,@dataType
\nEND<\/p>\n
DECLARE @Query nvarchar(4000) — provide for the whole query,
\n — you may increase the size<\/p>\n
SET @query =’SELECT ”’+substring(@string,0,len(@string)) + ‘)
\n VALUES(”+ ‘ + substring(@stringData,0,len(@stringData)-2)+”’+”)”
\n FROM ‘+@tableName
\nexec sp_executesql @query –load and run the built query<\/p>\n
CLOSE cursCol
\nDEALLOCATE cursCol<\/p>\n
GO<\/p>\n
[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"
A procedure abaixo cria query INSERT a partir dos registros de uma tabela no SQL Server. [sql] CREATE PROC [dbo].[InsertGenerator] (@tableName varchar(100)) as –Declare a cursor to retrieve column specific information –for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000) –for storing […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[4],"tags":[61,60,5],"jetpack_publicize_connections":[],"yoast_head":"\n