How can I generate an INSERT script for an existing SQL Server table that includes all stored rows

Question

I m looking for a way to generate a  Create and insert all rows  script with SQL Management Studio 2008 R2   I know that I can create a  create table  script   I can also create an  insert in  script  but that will only generate a single row with placeholders   Is there a way to generate an insert script that contains all currently stored rows

User · Answer

Yes  use the commercial but inexpensive SSMS Tools Pack addin which has a nifty  Generate Insert statements from resultsets  tables or database  feature

User · Answer

Yes  but you ll need to run it at the database level   Right-click the database in SSMS  select  Tasks    Generate Scripts       As you work through  you ll get to a  Scripting Options  section   Click on  Advanced   and in the list that pops up  where it says  Types of data to script   you ve got the option to select Data and or Schema

User · Answer

Just to share  I ve developed my own script to do it  Feel free to use it  It generates  SELECT  statements that you can then run on the tables to generate the  INSERT  statements   select distinct  SELECT   INSERT INTO     schema name ta schema id          so name          substring o list  1  len o list -1       VALUES      substring val list  1  len val list -1           FROM     schema name ta schema id          so name       from    sys objects so join sys tables ta on ta object id so object id cross apply  SELECT       column name         from information schema columns c  join syscolumns co on co name c COLUMN NAME and object name co id  so name and OBJECT NAME co id  c TABLE NAME and co id so object id and c TABLE SCHEMA SCHEMA NAME so schema id   where table name   so name  order by ordinal position FOR XML PATH      o  list  cross apply  SELECT        case          when data type    uniqueidentifier  THEN  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME                END            WHEN data type    timestamp  then           CONVERT NVARCHAR MAX  CONVERT BINARY 8       COLUMN NAME       1                     WHEN data type    nvarchar  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          REPLACE      COLUMN NAME                                      END           WHEN data type    varchar  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          REPLACE      COLUMN NAME                                      END           WHEN data type    char  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          REPLACE      COLUMN NAME                                      END           WHEN data type    nchar  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          REPLACE      COLUMN NAME                                      END           when DATA TYPE  datetime  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME      121           END            when DATA TYPE  datetime2  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME      121           END            when DATA TYPE  geography  and column name lt  gt  Shape  then  ST GeomFromText   POINT   column name   Lat   column name   Long                when DATA TYPE  geography  and column name  Shape  then           CONVERT NVARCHAR MAX       COLUMN NAME                          when DATA TYPE  bit  then           CONVERT NVARCHAR MAX       COLUMN NAME                          when DATA TYPE  xml  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          REPLACE CONVERT NVARCHAR MAX       COLUMN NAME                                       END            WHEN DATA TYPE  image  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX  CONVERT VARBINARY MAX       COLUMN NAME       1           END            WHEN DATA TYPE  varbinary  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME      1           END            WHEN DATA TYPE  binary  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME      1           END            when DATA TYPE  time  then  CASE WHEN      column name    IS NULL THEN   NULL   ELSE          CONVERT NVARCHAR MAX       COLUMN NAME                END            ELSE  CASE WHEN      column name    IS NULL THEN   NULL   ELSE CONVERT NVARCHAR MAX     column name     END  end               from information schema columns c  join syscolumns co on co name c COLUMN NAME and object name co id  so name and OBJECT NAME co id  c TABLE NAME and co id so object id and c TABLE SCHEMA SCHEMA NAME so schema id   where table name   so name  order by ordinal position FOR XML PATH      val  list  where   so type    U

User · Answer

This script generates insert statements of your existing data  This is a stored procedure which you need to run once and then it is tailor made for you   I tried to find this kind of stuff for a while but wasn t satisfied with the results  so I wrote this stored procedure   Example   Exec  dbo   INS    Dbo test where 1 1     1  Here dbo is schema and test is tablename and 1 1 is condition    Exec  dbo   INS    Dbo test where name    neeraj      for string    2  Here dbo is schema and test is tablename and name  neeraj  is condition   Here is the stored procedure     Authore   neeraj prasad sharma  please dont remove this     Example  1  Exec  dbo   INS    Dbo test where 1 1           2  Exec  dbo   INS    Dbo test where name    neeraj      for string  here Dbo is schema and test is tablename and 1 1 is condition       CREATE procedure   dbo   INS                                                                                                Query  Varchar MAX                                                                                             AS                                SET nocount ON                    DECLARE  WithStrINdex as INT                             DECLARE  WhereStrINdex as INT                             DECLARE  INDExtouse as INT                              DECLARE  SchemaAndTAble VArchar 270                              DECLARE  Schema name  varchar 30                              DECLARE  Table name  varchar 240                              DECLARE  Condition  Varchar MAX                                SET  WithStrINdex 0                              SELECT  WithStrINdex CHARINDEX  With   Query                                  WhereStrINdex CHARINDEX  WHERE    Query                               IF  WithStrINdex  0                              SELECT  INDExtouse  WithStrINdex                             ELSE                             SELECT  INDExtouse  WhereStrINdex                              SELECT  SchemaAndTAble Left   Query  INDExtouse-1                                                       SELECT  SchemaAndTAble Ltrim  Rtrim   SchemaAndTAble                                SELECT  Schema name  Left   SchemaAndTAble  CharIndex      SchemaAndTAble  -1                                      Table name   SUBSTRING    SchemaAndTAble   CharIndex      SchemaAndTAble   1 LEN  SchemaAndTAble                                         CONDITION SUBSTRING  Query  WhereStrINdex 6 LEN  Query  --27 6                               DECLARE  COLUMNS  table  Row number SmallINT   Column Name VArchar Max                                  DECLARE  CONDITIONS as varchar MAX                                DECLARE  Total Rows as SmallINT                               DECLARE  Counter as SmallINT                DECLARE  ComaCol as varchar max              SELECT  ComaCol                        SET  Counter 1                               SET  CONDITIONS                                   INSERT INTO  COLUMNS                               SELECT Row number  Over  Order by ORDINAL POSITION    Count   Column Name  FROM INformation schema columns  WHERE Table schema  Schema name AND table name  Table name            SELECT  Total Rows  Count 1   FROM  COLUMNS                                SELECT  Table name       Table name                            SELECT  Schema name      Schema name                            While   Counter lt   Total Rows                                 begin                                --PRINT  Counter                                SELECT  ComaCol   ComaCol     Column Name                  FROM  COLUMNS                               WHERE  Row number   Counter                            SELECT  CONDITIONS  CONDITIONS      Case When    Column Name    is null then   Null   Else            Replace  Convert varchar Max     Column Name                                   end                                                                FROM  COLUMNS                               WHERE  Row number   Counter                                SET  Counter  Counter 1                                End                                SELECT  CONDITIONS Right  CONDITIONS LEN  CONDITIONS -2                                 SELECT  CONDITIONS LEFT  CONDITIONS LEN  CONDITIONS -4                SELECT  ComaCol  substring   ComaCol 0   len  ComaCol                                 SELECT  CONDITIONS     INSERT INTO    Schema name      Table name       ComaCol        Values                CONDITIONS                                SELECT  CONDITIONS  CONDITIONS                                             SELECT  CONDITIONS   Select     CONDITIONS   FRom      Schema name      Table name   With NOLOCK        Where    Condition                               print  CONDITIONS                                Exec  CONDITIONS

[sql-server] How can I generate an INSERT script for an existing SQL Server table that includes all stored rows?

Examples related to sql-server

Examples related to ssms