By executing the following stored procedure we can create insert statement for all records in a table EXECUTE [InsertGenerator] ‘tableName’.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[InsertGenerator] (@tableName varchar(max)) 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(max) –for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) –for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(max) –data types returned for respective columns
SET @string=’INSERT ‘+@tableName+'(‘
SET @stringData=”
DECLARE @colName nvarchar(max)
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+’,””)+”””,”+’
SET @stringData=@stringData+””+”’+isnull(””’+””’+[‘+@colName+’]+””’+””’,”NULL”)+”,”+’
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’ –because money doesn’t get converted from varchar implicitly
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 –presuming the data type is int,bit,numeric,decimal
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(MAX)
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
Have questions? Contact the technology experts at InApp to learn more.
