IF OBJECT_ID('sp_GenerateInsertStatements', 'P') IS NOT NULL
     DROP PROCEDURE sp_GenerateInsertStatements
GO

CREATE PROCEDURE sp_GenerateInsertStatements
  @TableName NVARCHAR(255),
  @maxRows INT = 100

/*
     Prozedur:   sp_generateInsertStatements
     Version:    1.1
     letzte Änderung: 9/2002
     Info:       sql@itrain.de

     Parameter:
     @TableName   - der Name der Tabelle, für die INSERT-Anweisungen generiert
                    werden sollen
     @maxRows     - maximale Anzahl an Ausgabezeilen (Standardwert = 100)
*/
AS
SET NOCOUNT ON
DECLARE @INSERT_START NVARCHAR(4000)
DECLARE @COLUMN_LIST NVARCHAR(4000)
DECLARE @SELECT_LIST NVARCHAR(4000)
DECLARE @maxRowStatement NVARCHAR(20)
DECLARE @QuotedTableName NVARCHAR(257)
  SET @quotedTableName =  QUOTENAME(@TableName)
  SET @INSERT_START = 'INSERT INTO ' + @QuotedTableName + ' ('
  SELECT @COLUMN_LIST = CASE WHEN @COLUMN_LIST IS NULL THEN QUOTENAME(COLUMN_NAME)
       ELSE @COLUMN_LIST + ', ' + QUOTENAME(COLUMN_NAME)
         END
  FROM INFORMATION_SCHEMA.Columns
  WHERE TABLE_NAME = @TableName
    AND DATA_TYPE NOT IN ('text', 'ntext', 'image', 'timestamp', 'sql_variant')
  ORDER BY ORDINAL_POSITION
  SELECT @SELECT_LIST = CASE WHEN @SELECT_LIST IS NULL THEN 'ISNULL(CAST(master.dbo.fn_sqlvarbasetostr(' + QUOTENAME(COLUMN_NAME) + ') As NVARCHAR(4000)), ''NULL'')'
        ELSE @SELECT_LIST + '+ '',  '' + ISNULL(CAST(master.dbo.fn_sqlvarbasetostr(' + QUOTENAME(COLUMN_NAME) + ') as NVARCHAR(4000)), ''NULL'')'
          END
  FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName
   AND DATA_TYPE NOT IN ('text', 'ntext', 'image', 'timestamp', 'sql_variant')
  ORDER BY ORDINAL_POSITION

  SET @COLUMN_LIST = @COLUMN_LIST + ') VALUES ('
  SET @maxRowStatement = N'TOP ' + CAST(@maxRows As NVARCHAR(10))
  EXEC ('SELECT ' + @maxRowStatement + ' ''' + @INSERT_START + @COLUMN_LIST + ' '' + ' + @SELECT_List + '+ '')''' + '  FROM ' + @QuotedTableName)
GO
USE northwind
EXEC sp_generateInsertStatements 'orders', 2


-- Beispielaufruf von Commandline in Textdatei:
-- bcp "EXEC northwind.dbo.sp_generateinsertstatements 'orders', 20" queryout
--                                                         gen_orders.sql -w -S. -T

Link-Tipp zum Thema ".net Allgemein": www.4guysfromrolla.com/

Umfangreiche Site rund um ASP.net. Guter Startpunkt für dieses Thema