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
