Generate insert statements from table – SQL Server

If you are a database professional, you must have come across this situation, where you need to create an insert script containing data from a table. This is a very common task for a DBA or SQL developer. We can dynamically create an insert script, but in that case we need to take care of many things like identity value, datetime datatype, money or decimal datatype etc etc. I found a stored procedure called sp_generate_inserts developed by Narayana Vyas Kondreddi, which does the same job taking care of related constraints on its own while generating an insert script.

It has different parameters to handle different things. Parameter description and examples are available inside the stored procedure. Do check the message tab in result pane, every time you generate an insert script of a table. If there is an identity column then the commands gets printed over there to handle IDENTITY INSERTS.

I have modified the original stored proc little bit to make the insert statement as shown in the below snapshots. The reason doing this change is to, make the insert script more convenient for batch inserts. This sp gets created in master database and marked as a system object which means you can call this sp from any other databases available in the same SQL instance.

sp-generate-inserts-batch-sql-server

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

4 thoughts on “Generate insert statements from table – SQL Server

  • March 2, 2015 at 5:41 pm
    Permalink

    Nice one Prasad. It will help me to save data immediately from one server to another server. 🙂

    Reply
  • May 20, 2015 at 10:06 pm
    Permalink

    I just make an script called: SP GENERATE UPDATES, it would seemed to sp_generate_inserts that i’ve been using a long time ago. It will generate updates statements from a sql server table.

    — SP_GENERATE_UPDATES USAGE:

    — EXEC sp_generate_updates ‘Table_for_updates’, ”
    — Note: If the id_list variable is empty, it will generate a update statement for all rows

    — EXEC sp_generate_updates ‘Table_for_updates’, ‘123,124,125’
    — NOTE: it will generate three update queries that match those ids

    — ADVISE: it’s Only admit one primary key in the table
    — By MJM

    FYI:
    ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[sp_generate_updates] (@table_name varchar(256), @id_list varchar(max))
    as
    begin

    — SP_GENERATE_UPDATES USAGE:
    — EXEC sp_generate_updates ‘Table_for_updates’, ”
    — Note: If the id_list variable is empty, it will generate a update statement for all rows
    — EXEC sp_generate_updates ‘Table_for_updates’, ‘123,124,125’
    — NOTE: it will generate three update queries that match those ids
    — ADVISE: it’s Only admit one primary key in the table
    — By MJM

    declare @PK_COLUMN_NAME varchar(256)
    declare @PK_COLUMN_VALUE int
    declare @COLUMN_NAME varchar(512)
    declare @GET_PK_COLUMN cursor
    declare @GET_COLUMNS cursor
    declare @QUERY nvarchar(500)
    declare @QUERY2 nvarchar(500)
    declare @GET_ROWS_CURSOR CURSOR
    declare @sep varchar(3)
    declare @var varchar(max)
    declare @UPDATE_QUERY varchar(max)

    select @PK_COLUMN_NAME=column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_name = @table_name
    print ‘COLUMN NAME PK: ‘ + @PK_COLUMN_NAME
    if (@PK_COLUMN_NAME IS NULL)
    BEGIN
    print ‘You must define a primary key for ‘ + @table_name
    END

    if (@id_list ”)
    begin
    set @QUERY = ‘select ‘ + @PK_COLUMN_NAME + ‘ from ‘ + @table_name + ‘ where ‘ + @PK_COLUMN_NAME + ‘ in (‘ + @id_list + ‘)’
    end
    else
    begin
    set @QUERY = ‘select ‘ + @PK_COLUMN_NAME + ‘ from ‘ + @table_name
    end

    set @QUERY = ‘set @GET_ROWS_CURSOR = CURSOR FOR ‘ + @QUERY + ‘; OPEN @GET_ROWS_CURSOR’

    exec sp_executesql @QUERY, N’@GET_ROWS_CURSOR CURSOR OUTPUT’, @GET_ROWS_CURSOR OUTPUT

    FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @GET_COLUMNS = CURSOR
    FOR select column_name from information_schema.columns where table_name = @table_name
    open @GET_COLUMNS
    fetch next from @GET_COLUMNS into @COLUMN_NAME

    set @sep = ”
    set @UPDATE_QUERY = ”

    WHILE @@FETCH_STATUS = 0
    BEGIN
    if (@COLUMN_NAME @PK_COLUMN_NAME)
    BEGIN
    set @QUERY2 = ‘select @var=cast(‘ + @COLUMN_NAME + ‘ as varchar(max)) from ‘+@table_name+’ where ‘+ @PK_COLUMN_NAME + ‘ = ‘ + CAST(@PK_COLUMN_VALUE as varchar(9))
    exec sp_executesql
    @QUERY2,
    N’@var varchar(max) OUTPUT’,
    @var OUTPUT

    if (@var IS NOT NULL)
    begin
    set @UPDATE_QUERY = @UPDATE_QUERY + @sep + @COLUMN_NAME + ‘=”’ + replace(@var, ””, ”””) + ””
    set @sep = ‘, ‘
    end
    END

    fetch next from @GET_COLUMNS into @COLUMN_NAME
    END

    CLOSE @GET_COLUMNS
    DEALLOCATE @GET_COLUMNS

    print ‘update ‘ + @table_name + ‘ set ‘ + @UPDATE_QUERY + ‘ where ‘ + @PK_COLUMN_NAME + ‘ = ‘ + CAST(@PK_COLUMN_VALUE as varchar(9))

    FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
    END
    CLOSE @GET_ROWS_CURSOR
    DEALLOCATE @GET_ROWS_CURSOR
    end

    ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————

    Reply

Leave a Reply

%d bloggers like this: