This is one from the archives that obviously got away. I had need for this today so thought I’d post so I don’t have to look so hard next time!
It’s pretty much a case of create the SP in your chosen database and run with a tablename as the parameter. I originally found this on one of the SQL sites (probably SQLServerCentral), and amended it slightly to add some basic filtering in the @where parameter.
There’s a few limitations from memory – with blob, image and text fields, but for your average tables this will probably do the trick just fine.
I’ve also used a different method that basically builds up a single INSERT with SELECT ‘literal values’ UNION SELECT ‘literal values’. That works fine and is potentially easier to control transaction-wise as everthing’s in a single statement. Anyway here’s the code….
drop procedure [dbo].[usp_InsertGenerator]
GO
CREATE PROC
dbo.usp_InsertGenerator(@tableName varchar(100), @where varchar(1000) = NULL) 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+‘+””’+””’,”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(4000)
SET @query =‘SELECT ”’+substring(@string,0,len(@string)) + ‘) VALUES(”+ ‘ + substring(@stringData,0,len(@stringData)-2)+”’+”)” FROM ‘+@tableName
IF @where IS NOT NULL
SET @query = @query + ‘ where ‘ + @where
exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO
Colorized by: CarlosAg.CodeColorizer