Script All Insert Satements of the table in sql server 2000

75 views Asked by At

i wrote procedure that creates insert statements by passing it table name, but because of the limitation that sql server row size has, if your statement becomes more than row size, you can not have complete statement to run. so I want to have 2 rows for each statement , first row for insert into [tablename] (column list) and second row for values (). If anyone knows how to do that, please help me with that. thank you

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'p' AND name ='InsertGenerator') DROP PROCEDURE InsertGenerator
go

CREATE PROC InsertGenerator
(@tableName varchar(100)) 
as
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)   
DECLARE @stringData nvarchar(3000)
DECLARE @dataType nvarchar(1000) 
SET @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
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''N ULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else 
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as   varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' 
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+'   as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE 
IF @dataType='datetime'
BEGIN
SET   @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'

END
ELSE 
IF @dataType='image' 
BEGIN
SET  @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE 
BEGIN
SET  @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ '   +    substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--exec InsertGenerator 'ABI_SHOBE'
0

There are 0 answers