Creating A Script To Replicate A Table And Its Contents?

I know you can create a script to replic开发者_C百科ate a table using:

right click table > script table as > create to > new query editor window

But how can I generate a script that contains a bunch of insert commands for each row in the table?

Table1
Id1, Row1
Id2, Row2
Id3, Row3

Insert into Table1 values(Row1);
Insert into Table1 values(Row2);
Insert into Table1 values(Row3);

---

I ended up doing this

right click database > Tasks > Generate Scripts ... > selected the tables > in the advanced options I set "Types of data to script" to "Schema and data"

---

Select
  'Insert into Table (
     IntField1
     StringField2
     Column3)
   values (' +
     IntField1 + ',' +
     + '''' + StringField2 + ''',' +
     Column2 + ')'  as InsertQuery
From Table

Something like this, just remember if your string contains a single quote you will need to make sure you replace it like this replace(stringfield, '''', '''''')

---

So this isnt super pretty cuz I kind of took one of my sp's and hacked it up for this. But basically this will take any table and print a series of insert statements into a table called tbl_text (which you would need to create)

The arguments are the table name and the table ID from sysobjects

--this is how you get the tbl_id
SELECT id FROM sysobjects WHERE type = 'U' AND name = 'tablename'


CREATE PROCEDURE dbo.sp_export_table 
@tblhdr varchar(100), 
@tblID varchar(100)
AS

SET NOCOUNT ON

IF object_id('tempdb..##temptable') IS NOT NULL
BEGIN
   DROP TABLE ##temptable
END

DECLARE @identity bit
DECLARE @typestmt nvarchar(100)
DECLARE @typeval int
DECLARE @rowstmt nvarchar(1000)
DECLARE @rowID varchar(50)
DECLARE @orderby nvarchar(100)
DECLARE @clmnstmt varchar(200)
DECLARE @clmnhdr varchar(50)
DECLARE @clmnstring varchar(1000)
DECLARE @valuestmt nvarchar(200)
DECLARE @valuestring nvarchar(3000)
DECLARE @value nvarchar(1000)
DECLARE @insertstmt varchar(1000)
DECLARE @params nvarchar(100)
DECLARE @param2 nvarchar(100)
SELECT @rowstmt = N'SELECT TOP 1 @inside_var = name FROM syscolumns WHERE id = ' + @tblID + ' ORDER BY colorder'
SELECT @params = N'@inside_var  NVARCHAR(1000) OUTPUT'
EXEC sp_executesql @rowstmt, @params, @inside_var = @orderby OUTPUT

SELECT @rowstmt = 'SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @orderby + ') AS row INTO ##temptable FROM ' + @tblhdr
exec(@rowstmt)


IF object_id('tempdb..##temptable') IS NOT NULL
BEGIN
    DECLARE row_cursor CURSOR FOR
    SELECT row FROM ##temptable

    OPEN row_cursor

    FETCH NEXT FROM row_cursor
    INTO @rowID

    --if table has identity and has records write identity_insert on
    SET @identity = 0
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),
    'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME = @tblhdr) AND EXISTS(SELECT * FROM ##temptable)
    BEGIN
        SET @identity = 1
        INSERT INTO dbo.tbl_text VALUES('SET IDENTITY_INSERT dbo.' + @tblhdr + ' ON')
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @clmnstmt = 'DECLARE column_cursor CURSOR FOR SELECT name FROM syscolumns WHERE id = ' + @tblID + ' ORDER BY colorder'
        exec(@clmnstmt)

        OPEN column_cursor

        FETCH NEXT FROM column_cursor
        INTO @clmnhdr

        SELECT @clmnstring = '('
        SELECT @valuestring = '('

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @clmnhdr <> 'row'
            BEGIN
                SELECT @clmnstring = @clmnstring + @clmnhdr + ','
                SELECT @valuestmt = N'SELECT @inside_var = ' + @clmnhdr + ' FROM ##temptable WHERE row = ' + @rowID
                EXEC sp_executesql @valuestmt, @params, @inside_var = @value OUTPUT

                SELECT @typestmt = N'SELECT @inside_var2 = xtype FROM syscolumns WHERE name = ''' + @clmnhdr + ''' AND id = ' + @tblID
                SELECT @param2 = N'@inside_var2 INT OUTPUT'
                EXEC sp_executesql @typestmt, @param2, @inside_var2 = @typeval OUTPUT

                IF @typeval NOT IN (48,52,56,59,60,62,104,108,122,127)
                BEGIN
                    SET @value = REPLACE(@value,'''','''''')
                    SET @value  = '''' + @value + ''''
                    SET @value = ISNULL(@value, '''''')
                END

                IF NOT (@typeval = 34)
                BEGIN
                    SELECT @valuestring = @valuestring + @value + ','
                END 
                ELSE
                BEGIN
                    SELECT @valuestring = @valuestring + '''''' + ','
                END
            END

            FETCH NEXT FROM column_cursor
            INTO @clmnhdr

        END

        SET @clmnstring = LEFT(@clmnstring, LEN(@clmnstring) - 1)
        SET @valuestring = LEFT(@valuestring, LEN(@valuestring) - 1)

        INSERT INTO dbo.tbl_text VALUES('INSERT INTO dbo.' + @tblhdr + ' ' + @clmnstring + ') VALUES' + @valuestring + ')')


        FETCH NEXT FROM row_cursor
        INTO @rowID

        CLOSE column_cursor
        DEALLOCATE column_cursor

    END

    --if it wrote identity_insert on, turn it off
    IF (@identity = 1)
    BEGIN
            INSERT INTO dbo.tbl_text VALUES('SET IDENTITY_INSERT dbo.' + @tblhdr + ' OFF')
    END 

    CLOSE row_cursor
    DEALLOCATE row_cursor   
END

IF object_id('tempdb..##temptable') IS NOT NULL
BEGIN
    DROP TABLE ##temptable
END

GO

---

If you've got an account on SSC, you can use the script I published last year. It works without cursors an it enables custom filtering.

http://www.sqlservercentral.com/scripts/Script+Data/65998/

Hope this helps

---

Assuming Row is an INT NOT NULL. You could write a SELECT statement that outputs SQL;

SELECT N'INSERT INTO Table1 VALUES (' + CAST(Row AS NVARCHAR(10)) + N');'
FROM Table1

Then output your results to text.