Cybercrux

Everything is achievable through technology

Deploy data

Automated Insert query with data


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

drop PROC Generate_Insert_Statements
go

CREATE PROC Generate_Insert_Statements
(@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 the first half of INSERT statement
DECLARE @stringData nvarchar(3000) –for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) –data types returned for respective columns
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+’,””)+”””,”+’
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(cast(‘+@colName+’ as varchar(200)),”0”)+”””),”+’
–SELECT ‘INSERT Authorizations(StatusDate) VALUES(‘+’convert(datetime,’+isnull(””+convert(varchar(200),StatusDate,121)+””,’NULL’)+’,121),)’ FROM Authorizations
–SET @stringData=@stringData+”’convert(money,”””+isnull(cast(‘+@colName+’ as varchar(200)),”0.0000”)+”””),”+’
SET @stringData=@stringData+”’convert(datetime,’+”’+isnull(””’+””’+convert(varchar(200),’+@colName+’,121)+””’+””’,”NULL”)+”,121),”+’
— ‘convert(datetime,’+isnull(””+convert(varchar(200),StatusDate,121)+””,’NULL’)+’,121),)’ FROM Authorizations
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(cast(‘+@colName+’ as varchar(200)),”0”)+”””,”+’
–SET @stringData=@stringData+”’convert(datetime,’+”’+isnull(””’+””’+convert(varchar(200),’+@colName+’,121)+””’+””’,”NULL”)+”,121),”+’
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
–select @query

CLOSE cursCol
DEALLOCATE cursCol
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s