Here's a simple enough procedure to generate an insert statement for a given table. I know you can do this through SSMS, but I dislike having to navigate through the tree to get to the table I want.

This procedure gives you the option to include descriptions in the VALUES portion of the insert statemnt, or leave them out. You could also modify the procedure to include default NULLs if you wish, or to include Identity columns, or whatever you like.

 
CREATE PROCEDURE proc_CreateInsertForTable
  @tableName nvarchar(1000),
  @withDescription bit = 1
AS 
/*************************************************************************** 
  Written by Jennifer McCown, 9/13/2009 
  Jen@JenniferMcCown.com 
  http://www.MidnightDBA.com 
  Not copywrited; use, modify, and distribute freely! 
  
  Description: Generate an insert statement for a given table.  
  @withDescription controls the appearence of the "VALUES" portion of the 
  generated input statement.
   
  Please note that the insert statement will not include Identity columns. 

  EXEC exec proc_CreateInsertForTable @tableName = 'Video', @withDescription = 1
****************************************************************************/

SET NOCOUNT ON

DECLARE @sql1 varchar(8000), 
	@sql2 varchar(4000),
	@name varchar(500), 
	@type varchar(100)
	
DECLARE @mytable table (column_id int, 
		[name] nvarchar(500), 
		system_type_id int, 
		max_length int, 
		[precision] int, 
		scale int, 
		is_nullable bit, 
		is_identity bit, 
		readableType  varchar(100))

INSERT INTO @myTable
SELECT column_id, 
	c.[name], 
	c.system_type_id, 
	c.max_length, 
	c.[precision], 
	c.scale, 
	c.is_nullable, 
	c.is_identity, 
	t.[name] readableType
from sys.columns c
JOIN sys.types t on c.system_type_id = t.system_type_id
where object_name(c.object_id) = 'Video' order by c.column_id

-- Update ReadableType columns
UPDATE @myTable SET readableType = readableType 
	+ ' (' + CAST(max_length as nvarchar(10)) + ')'
WHERE readableType IN ('char', 'varchar', 'nchar', 'nvarchar')

UPDATE @myTable SET readableType = readableType 
	+ ' (' + CAST([precision] as nvarchar(10))  
	+ ', ' + CAST([scale] as nvarchar(10))  + ')'
WHERE readableType IN ('decimal','numeric')

UPDATE @myTable SET readableType = readableType 
	+ ' (' + CAST([precision] as nvarchar(10))  + ')'
WHERE readableType = 'float'


-- Seed the SQL variables with INSERT syntax
SET @sql1 = 'INSERT INTO ' + @tableName + ' (' 
SET @sql2 = ') ' + 'VALUES (' 


-- Open a cursor to add all the columns to INSERT statement
DECLARE insertCrs CURSOR FOR
SELECT [name], readableType from @myTable WHERE is_identity = 0

OPEN insertCrs 

-- Get first column
FETCH NEXT FROM insertCrs INTO @name, @type
SET @sql1 = @sql1 + @name
IF @withDescription = 1 
  SET @sql2 = @sql2 + ''''' /** ' + @name + ' ' + @type + ' **/ '
ELSE 
  SET @sql2 = @sql2 + ''''' '

-- Add additional columns
FETCH NEXT FROM insertCrs INTO @name, @type

  IF @withDescription = 1 
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  SET @sql1 = @sql1 + ', ' + @name
	  SET @sql2 = @sql2 + ', '''' /** ' + @name + '**/ '
	  FETCH NEXT FROM insertCrs INTO @name, @type
	END
  
  ELSE

	WHILE @@FETCH_STATUS = 0
	BEGIN
	  SET @sql1 = @sql1 + ', ' + @name
	  SET @sql2 = @sql2 + ', '''' '
	  FETCH NEXT FROM insertCrs INTO @name, @type
	END


CLOSE insertCrs
DEALLOCATE insertCrs

SET @sql2 = @sql2 + ')'

SELECT @sql1, @sql2

SET NOCOUNT OFF
RETURN 0

Author Credits

Jen McCown

This wiki article was written by Jennifer McCown.

Jen McCown is a SQL Server developer and DBA with over 10 years experience. She writes book and product reviews for ITBookworm.com, makes training videos and blogs for MidnightDBA.com, and maintains both websites.

Her online presences include:

• Blog: http://MidnightDBA.ITBookworm.com/blog/
• Videos: http://www.MidnightDBA.com/
• Twitter: http://Twitter.com/MidnightDBA