?
Solved

Insert queries from data

Posted on 2005-04-10
5
Medium Priority
?
1,076 Views
Last Modified: 2013-11-30
Dear DBExperts,

Is there any way to convert data stored in SQL server tables into insert queries.

Thought my target is to simple data export, but due to some limitations I cannot use DTS (import/export).

I think only thing which can solve my problem is some tool, which reads the data from table and create insert queries.


Regards,
Me
0
Comment
Question by:fahimnxb
5 Comments
 
LVL 8

Accepted Solution

by:
Julianva earned 2000 total points
ID: 13750818
Run this query on the pubs database you will see what it
does



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


EXEC DataAsInsCommand 'employee,titleauthor,pub_info'
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13750897
Generate Insert Statements Easily

http://www.databasejournal.com/scripts/article.php/1502051


itsvtk
0
 
LVL 10

Expert Comment

by:imrancs
ID: 13750923
Hello Fahim,

Why don't you use BACKUP and RESTORE.


Regards,

Imran@NXB :o)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13762680
creating an insert statement for every row in your db sounds like a really dumb idea IMHO.   SQL 2000 will have been decomissioned by the time all that sql executes :).    If you want to move the whole db do as imran suggests,  if you just want data then bcp it out and bcp it back in.

0
 
LVL 5

Author Comment

by:fahimnxb
ID: 13916605
Dear ImranCS,

"Sitaron say agay jahan or bhee hain :)" Well I am really happy to hear from you, its a long time passed to have some chit chat.

Sorry experts - we are old collegues. Imran its a customized requirement lemme try suggestions of other experts.

Regards,
Me
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question