SQL Nugget - shortcut to create INSERT into

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

set nocount on
DECLARE @sTableName varchar(128)set @sTableName = '%TableName%'
select @sTableName as sTableName into #tmpTableName
DECLARE @lTableID intset @lTableID = NullSELECT @lTableID = [ID] from sysobjects where (objectproperty(id, N'IsTable') = 1) and (id = object_id(@sTableName))
if (@lTableID is Null)begin print 'Table not found! Aborting.' returnend
SELECT [name], xtype, prec, scale, colorder, isnullable into #tmpColumns from syscolumns where ([id] = @lTableID) and (colstat = 0) order by colorder
alter table #tmpColumns ADD lRowID int not null identity, -- add an identity column to the temp table sDefault varchar(40) -- add a column to store the default that we want to enter for new rowsGO
UPDATE #tmpColumns set sDefault = case xtype when 34 then ''' ''' -- image when 35 then ''' ''' -- text when 36 then Null -- unique identifier when 48 then '0' -- tinyint when 52 then '0' -- smallint when 56 then '0' -- int when 58 then '''1/1/1900''' -- smalldatetime when 59 then '0.0' -- real when 60 then '0' -- money when 61 then '''1/1/1900''' -- datetime when 62 then '0.0' -- float when 99 then ''' ''' -- ntext when 104 then '0' -- bit when 106 then '.00' -- decimal when 108 then '0.0' -- numeric when 122 then '0.0' -- smallmoney when 165 then '0' --'convert(varbinary, '' '')' -- varbinary when 167 then ''' ''' -- varchar when 173 then '0' --'convert(binary, '' '')' -- binary when 175 then ''' ''' -- char when 189 then Null -- timestamp when 231 then ''' ''' -- nvarchar when 239 then ''' ''' -- nchar endDELETE #tmpColumns where sDefault is Null--select sDefault, xtype, [name] from #tmpColumns order by colorder, lRowID

DECLARE cur insensitive scroll cursor for select sDefault, [name] from #tmpColumns order by colorder, lRowIDOPEN cur
declare @sDefault varchar(40), @sName varchar(128)declare @sWork varchar(200)declare @sWork2 varchar(100)
select top 1 @sWork2 = sTableName from #tmpTableNameprint 'INSERT into ' + @sWork2print ' ('
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sName
FETCH next from cur into @sDefault, @sName -- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
print @sWorkendprint ' )'
print 'select'
FETCH first from cur into @sDefault, @sNameWHILE ( @@fetch_status = 0 )begin set @sWork = char(9) + @sDefault set @sWork2 = @sName
-- get the next record from the cursor FETCH next from cur into @sDefault, @sName
-- if the fetch is good, add a ',' to the end if ( @@fetch_status = 0 ) set @sWork = @sWork + ','
set @sWork = @sWork + char(9) + '-- ' + @sWork2 print @sWorkend
CLOSE curDEALLOCATE CUR
DROP table #tmpColumnsDROP table #tmpTableName

http://mbsguru.blogspot.com/2006/04/sql-nugget-shortcut-to-create-insert.html