Generate INSERT statments for tables

Generate Insert Statements for tables, a very handy script you will like to use.

 
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