Binaryworld - Generate Insert Statements ... [ SQL Server -> Stored Procedure ]

/*
This script will generate insert statements for the given tables. You can pass
the tables names into DataAsInsCommand stored procedure separated by commas,
as in the example below:

EXEC DataAsInsCommand 'employee,titleauthor,pub_info'

Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This script will generate insert statements for the given tables.
You can pass the tables names into DataAsInsCommand stored procedure
separated by commas, as in the example below:

EXEC DataAsInsCommand 'employee,titleauthor,pub_info'

It can be used if you want to transfer your data at the new place.
You can first generate script with the tables creation and after that
run the results set of the DataAsInsCommand stored procedure to
insert rows. By the way, more proper way to do it - is generate DTS package.
My stored procedure can be used for learning some general SQL Server
features (how to work with string functions, how to work with unknown
number of parameters, passed into stored procedure, how to work with
cursors and so on).
*/

IF OBJECT_ID('DataAsInsCommand') IS NOT NULL DROP PROC DataAsInsCommand
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC DataAsInsCommand (
@TableList varchar (200))
AS
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (2000), @TableName varchar (30)
DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint
SELECT @TableList = @TableList + ','
SELECT @IsIdentity = 0
SELECT @position = PATINDEX('%,%', @TableList)
WHILE (@position <> 0)
BEGIN
  SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
  SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList), '')
  SELECT @position = PATINDEX('%,%', @TableList)

  SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR '
   + 'SELECT name, xtype, status FROM syscolumns WHERE id = object_id("'
   + @TableName + '")'
  EXEC (@exec_str)

  OPEN fetch_cursor
  FETCH fetch_cursor INTO @name, @xtype, @status
  IF (@status & 0x80) <> 0
   BEGIN
    SELECT @IsIdentity = 1
    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
    SELECT 'GO'
   END
  SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "

  --text or ntext
  IF (@xtype = 35) OR (@xtype = 99)
    SELECT @exec_str = @exec_str + '''"None yet"'''
  ELSE

  --image
  IF (@xtype = 34)
    SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
  ELSE

  --smalldatetime or datetime
  IF (@xtype = 58) OR (@xtype = 61)
    SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
  ELSE

  --varchar or char or nvarchar or nchar
  IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
    SELECT @exec_str = @exec_str + '''"'' + ' + @name + ' + ''"'''
  ELSE

  --uniqueidentifier
  IF (@xtype = 36)
    SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
  ELSE

  --binary or varbinary
  IF (@xtype = 173) OR (@xtype = 165)
    SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
  ELSE

    SELECT @exec_str = @exec_str + 'ISNULL(CONVERT(varchar,' + @name + '), "null")'

  WHILE @@FETCH_STATUS <> -1
   BEGIN
    FETCH fetch_cursor INTO @name, @xtype, @status
    IF (@@FETCH_STATUS = -1) BREAK
    IF (@status & 0x80) <> 0
     BEGIN
      SELECT @IsIdentity = 1
      SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
      SELECT 'GO'
     END

    --text or ntext
    IF (@xtype = 35) OR (@xtype = 99)
      SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''
    ELSE

    --image
    IF (@xtype = 34)
      SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0xFFFFFFFF' + '"'
    ELSE

    --smalldatetime or datetime
    IF (@xtype = 58) OR (@xtype = 61)
      SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
    ELSE

    --varchar or char or nvarchar or nchar
    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
      SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"'' + ' + @name + ' + ''"'''
    ELSE

    --uniqueidentifier
    IF (@xtype = 36)
      SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
    ELSE

    --binary or varbinary
    IF (@xtype = 173) OR (@xtype = 165)
      SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' + '"'
    ELSE

      SELECT @exec_str = @exec_str + ' + ","' + ' + ISNULL(CONVERT(varchar,' + @name + '), "null")'
   END

  CLOSE fetch_cursor
  DEALLOCATE fetch_cursor

  SELECT @exec_str = @exec_str + '+ ")" FROM ' + @TableName
  SELECT @exec_str
  EXEC(@exec_str)
  SELECT 'GO'

  IF @IsIdentity = 1
    BEGIN
     SELECT @IsIdentity = 0
     SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
     SELECT 'GO'
    END
END
GO