Script Table Data to Insert Statements

  • Tatsu

    SSCertifiable

    Points: 7824

    Comments posted to this topic are about the item Script Table Data to Insert Statements

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Alexander Zeitler

    Newbie

    Points: 7

    Hi,

    should this script also work with SQL Server 2000 databases?

    I always get the error 'invalid object name'.

    Alex

  • kuldipMCA

    Old Hand

    Points: 328

    This is NIce script but when ever NULL comes it's not working

    and we need GO at the end of the every insert statement

    so please add the go statement and send me on my mail id

    kuldip.bhatt@digi-corp.com

    i am not DBA but i want Learn this type script if any send me

    and i am also get Help from your side

  • devavejay-806985

    SSC Rookie

    Points: 39

    This was awesome once i figured out i had to drop the schema.table name syntax from

    select c.name, c.xtype, c.status

    from syscolumns c

    inner join sysobjects o

    on o.id = c.id

    where o.name = 'CalendarCountry'

    and o.xtype in ('U', 'S')

    order by ColID

    just a fyi

  • Furby

    Valued Member

    Points: 63

    Many thanks for the script.

    For anyone interested - In order to get the datetime stamps to return seconds (and milliseconds) I had to change the following:

    else if @ColType in (58, 61) -- dates (nulls not supported yet)

    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''

    TO

    else if @ColType in (58, 61) -- dates (nulls not supported yet)

    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ',13)+'''''''

    Of course any CONVERT style code can be used.

  • kermitgreen

    SSC Rookie

    Points: 31

    Brilliant solution to scripting table data. I ran into trouble with one column name "%Target" so, I added square brackets around @ColName and that fixed my issue.

    begin

    set @ColList = @ColList + ' ' + '['+ @ColName + ']'

    if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet)

    set @ValList = @ValList + ' ''+convert(varchar(200),' + '[' + @ColName + ']' + ')+'''

    else if @ColType in (175, 239, 231, 231, 167) -- uid and string types

    set @ValList = @ValList + ' ''''''+isnull(' + '[' + @ColName + ']' + ',''null'')+'''''''

    else if @ColType in (58, 61) -- dates (nulls not supported yet)

    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + '[' + @ColName +']' + ')+'''''''

    else if @ColType = 36 -- uniqueidentfiers (nulls not supported yet)

    set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + '[' + @ColName + ']' + ')+''}'''''

    if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end

    if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    end

    Thanks,

    Johnny

  • LAJ

    SSC Rookie

    Points: 25

    To handle nulls, I changed the code to the following:

    OLD CODE

    set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''

    NEW CODE

    set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''

    I actually converted the script to a stored procedure, I probably wouldn't recommend this in a production environment, but for development it is okay.

    CREATE PROCEDURE [dbo].[Script_Table]

    (

    @TableName sysname,

    @IdentityInsert int = 0,

    @DebugMode bit = 0

    )

    AS

    SET NOCOUNT ON

    --declare @TableName sysname

    declare @WhereClause varchar(1024)

    --declare @IdentityInsert int

    declare @ColName sysname

    declare @ColType tinyint

    declare @ColStatus tinyint

    --declare @DebugMode bit

    declare @ColList varchar(8000)

    declare @ValList varchar(8000)

    declare @SQL1 nvarchar(4000)

    declare @SQL2 nchar(10)

    declare @SQL3 nchar(1000)

    --set @TableName = 'emp' -- ''

    set @WhereClause = '' -- limit scope of inserts

    --set @DebugMode = 0 -- set to 1 if you only want a script

    --set @IdentityInsert = 1 -- set to 1 if you want to force IDENTITY_INSERT statements

    set @ColList = ''

    set @ValList = ''

    set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('

    set @SQL2 = ') values ('

    set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName

    if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3

    /*

    select c.name, c.xtype, c.status

    from syscolumns c

    inner join sysobjects o

    on o.id = c.id

    where o.name in (@TableName)

    and o.xtype in ('U', 'S')

    order by ColID

    */

    declare csrColumns cursor local fast_forward for

    select c.name, c.xtype, c.status

    from syscolumns c

    inner join sysobjects o

    on o.id = c.id

    where o.name = @tablename

    and o.xtype in ('U', 'S')

    order by ColID

    open csrColumns

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    while @@fetch_status = 0

    begin

    if not ((@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0))

    set @ColList = @ColList + ' ' + @ColName

    if @ColType in (127,173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet)

    if(@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0)

    set @ValList = @ValList

    else

    set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''

    else if @ColType in (175, 239, 231, 231, 167) -- uid and string types

    set @ValList = @ValList + ' ''''''+isnull(replace([' + @ColName + '],'''''''',''''''''''''),''null'')+'''''''

    else if @ColType in (36,58, 61) -- dates , uniqueidentifiers(nulls not supported yet)

    set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''

    if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end

    if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    end

    close csrColumns

    deallocate csrColumns

    set @ColList = replace(ltrim(@ColList), ' ', ', ')

    set @ValList = replace(ltrim(@ValList), ' ', ', ')

    if @IdentityInsert = 1

    print 'set identity_insert ' + @TableName + ' on'

    if @DebugMode = 1

    print + @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause

    else

    exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)

    if @IdentityInsert = 1

    print 'set identity_insert ' + @TableName + ' off'

    SET NOCOUNT OFF

    GO

  • lifuhai

    SSC Rookie

    Points: 46

    Hi guys, I think this is pretty useful when we dont have trust connection to other server and want to transfer some bunch of data to there. actually I updated the collation stuff, and all the others kind of we may have same or similar updates. hope some guy can finish the to do list I stated in the scripts.

    the scripts is here:

    /*

    Use this script to create insert statements for each row in the specified table.

    Instructions:

    1. Set the database you want to script from as normal.

    2. change the set @TableName = '<YourTableName>' line to be the

    table you want to script out.

    3. Run the script and copy all the text from the results below

    the line with all the dashes (----).

    Notes:

    If you get the error message "Invalid object name '<YourTableName>'."

    then you either forgot to set the correct database or you spelled

    your table name wrong

    Credits:

    Bob Wiechman - Fix for smalldatetime support

    Richard Lesh - correct support of uniqueidentifiers, automatic

    setting of Identity off/on, add Where clause support, more detail in

    debug mode.

    Fuhai Li - correct support of null values;

    fix the coallation conflicts via using default coallation

    correct the identity insert on/off for non debug mode

    column name with bracket to make it robust for naming

    To do: test the null values for gui type

    To do: add on parameters to choose which fields would export via colorder such as 1,2,...

    */

    declare @TableName sysname

    declare @WhereClause varchar(1024)

    declare @IdentityInsert int

    declare @ColName sysname

    declare @ColType tinyint

    declare @ColStatus tinyint

    declare @DebugMode bit

    declare @ColList nvarchar(4000)

    declare @ValList nvarchar(4000)

    declare @SQL1 nvarchar(1000)

    declare @SQL2 nchar(10)

    declare @SQL3 nchar(1000)

    set @TableName = 'Region' -- '<YourTableName>'

    set @WhereClause = '' -- limit scope of inserts, this will be hard coded thing to narrow down the set

    set @DebugMode = 0 -- set to 1 if you only want a script

    set @IdentityInsert = 0 -- set to 1 if you want to force IDENTITY_INSERT statements

    set @ColList = ''

    set @ValList = ''

    set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('

    set @SQL2 = ') values ('

    set @SQL3 = ')'' COLLATE DATABASE_DEFAULT, ''''''null'''''', ''null'') from ' + @TableName

    if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3

    declare csrColumns cursor local fast_forward for

    select c.name, c.xtype, c.status

    from syscolumns c

    inner join sysobjects o

    on o.id = c.id

    where o.name = @TableName

    and o.xtype in ('U', 'S')

    order by ColID

    open csrColumns

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    /*

    while @@fetch_status = 0

    begin

    set @ColList = @ColList + ' ' + @ColName

    if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet)

    set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''

    else if @ColType in (175, 239, 231, 231, 167) -- uid and string types

    set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''

    else if @ColType in (58, 61) -- dates (nulls not supported yet)

    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''

    else if @ColType = 36 -- uniqueidentfiers (nulls not supported yet)

    set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + @ColName + ')+''}'''''

    if @DebugMode = 1 begin print '-- @ValList: ' + rtrim(@ValList) end

    if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    end

    */

    while @@fetch_status = 0

    begin

    --set @ColList = @ColList + ' ' + @ColName

    set @ColList = @ColList + ' ' + '['+ @ColName + ']'

    if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types

    set @ValList = @ValList + ' ''+isnull(convert(varchar(200),' + @ColName + '),''null'')+'''

    else if @ColType in (175, 239, 231, 231, 167) -- uid and string types

    set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''

    else if @ColType in (58, 61, 36) -- dates and uniqueidentfiers

    set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),' + @ColName + '),''null'')+'''''''

    if @DebugMode = 1 begin print '-- @ValList: ' + @ValList end

    if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end -- Check if column has Identity attribute

    fetch next from csrColumns into @ColName, @ColType, @ColStatus

    end

    close csrColumns

    deallocate csrColumns

    set @ColList = replace(ltrim(@ColList), ' ', ', ')

    set @ValList = replace(ltrim(@ValList), ' ', ', ')

    if @IdentityInsert = 1 and @DebugMode = 1

    print 'set identity_insert ' + @TableName + ' on'

    if @IdentityInsert = 1 and @DebugMode = 0

    select 'set identity_insert ' + @TableName + ' on'

    if @DebugMode = 1

    print @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause

    else

    exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)

    if @IdentityInsert = 1 and @DebugMode = 1

    print 'set identity_insert ' + @TableName + ' off'

    if @IdentityInsert = 1 and @DebugMode = 0

    select 'set identity_insert ' + @TableName + ' off'

    Regards,

    Fuhai

  • titan2782

    SSC Enthusiast

    Points: 133

    Doesnt work. I just get a rowset of n rows (n being the total records in the target table) of an insert statement with no columns and no data.

  • David Masciangelo

    SSC Veteran

    Points: 204

    titan2782 (6/10/2010)


    Doesnt work. I just get a rowset of n rows (n being the total records in the target table) of an insert statement with no columns and no data.

    I had the same problem, don't use the schemaName.tableName format, just use the table name.

  • SQL_ME_RICH

    SSChampion

    Points: 11200

    Tried this script on a 2005 SP3 system, to try and construct insert statements for a rather large table of data (hundreds of millions of rows). Needless to say - it didn't work, and through and out of memory exception error in the messages section of the results at about 33 million. :-/

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply