Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Generate SQL insert statement fro SQL Server database

    Good day,

    I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.

    Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?

    Please someone help, this is driving me insane.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Found the answer, great sacript.

    SQL Insert generator script - http://www.planet-source-code.com/vb...=1052&lngWId=5

  3. #3
    Join Date
    Jun 2006
    Posts
    1
    I wrote this a while back for my own use
    Call this sp in a loop over
    Code:
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables
    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    CREATE PROCEDURE spGenerateInsertStatement
    (
           @TableName varchar(255),
           @SQL_INSERT varchar(8000) OUTPUT
    )
    AS
    
    DECLARE @current_column  varchar(255)
    DECLARE @current_ordinal int
    DECLARE @max_ordinal     int
    
    --pre assign some values for safety
    SET @current_column  = ''
    SET @current_ordinal = 0
    SET @max_ordinal     = 0
    
    SET @SQL_INSERT      = 'INSERT INTO [' +  @TableName + '] ('
    
    --get the max ordinal for the table you're inserting into
    SELECT @max_ordinal = MAX(ORDINAL_POSITION)
      FROM INFORMATION_SCHEMA.columns
     WHERE TABLE_NAME = @TableName
    
    --create a cursor of column names and ordinals
    DECLARE cInsert CURSOR
    FOR
      SELECT COLUMN_NAME,
             ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.columns
       WHERE TABLE_NAME = @TableName
    ORDER BY ORDINAL_POSITION
    
    OPEN cInsert
    
    --fetch the next record
    FETCH NEXT FROM cInsert INTO @current_column,@current_ordinal
    
    WHILE(@@FETCH_STATUS = 0)
    BEGIN
    
           
           IF(@current_ordinal != @max_ordinal)           
                  BEGIN
                         --append "column," to the insert statement
                         SET @SQL_INSERT = @SQL_INSERT + '[' +@current_column +  '],' + CHAR(13) + CHAR(10)
                  END
           ELSE
                  BEGIN 
                         --append "column)" to the insert statement
                         SET @SQL_INSERT = @SQL_INSERT + '[' + @current_column + '])' + CHAR(13) + CHAR(10)
                  END
    
           --fetch the next record       
           FETCH NEXT FROM cInsert INTO @current_column,@current_ordinal
    END
    
    --cleanup cursor
    CLOSE      cInsert
    DEALLOCATE cInsert
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO

  4. #4
    Join Date
    Jun 2006
    Posts
    2
    Hi! I'm on your side now!

  5. #5
    Join Date
    Jun 2006
    Posts
    2

    Wink Just trying to learn more!

    Hi! I just joined the forum to learn more about sql server 2000 and make friends too!

  6. #6
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Raski, what do you mean you are on our side??? Where you from?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •