Pages

Tuesday, January 12, 2010

Generate Insert statement for multiple tables dynamically

DROP PROC InsertGenerator
go
CREATE PROC 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,table_schema 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
DECLARE @schemaType nvarchar(100) --table schema value for correct schema execution
SET @stringData=''
DECLARE @Query nvarchar(4000)


DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0
BEGIN
SET @stringData=@stringData + @colName+','
--SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType,@schemaType
END
set @stringData = substring(@stringData,0,len(@stringData))
SET @string='INSERT INTO '+@schemaType+'.'+@tableName+'(' +@stringData +')'
SET @query =@string +'SELECT '+@stringData+' FROM '+@schemaType+'.'+@tableName
Print @query
--exec sp_executesql @query

--select @query

CLOSE cursCol
DEALLOCATE cursCol


GO

Run: InsertGenerator @tablename

No comments:

Post a Comment