Converting Select results into Insert script - SQL Server

Question

I have SQL Server 2008  SQL Server Management Studio   I need to select data from Table1 in database1  Then I have to edit some values in the results and insert values into Table1 in database2    Or let me put it other way    How can I convert the data in one table into insert script

User · Answer

Here is another method  which may be easier than installing plugins or external tools in some situations    Do a select  whatever you need INTO temp table namefrom      etc       Right-click on the database in the Object Explorer    Tasks    Generate Scripts Select temp table name in the  Choose Objects  screen  click Next  In the  Specify how scripts should be saved  screen    Click Advanced  find the  Types of data to Script  property  select  Data only   close the advanced properties  Select  Save to new query window   unless you have thousands of records    Click Next  wait for the job to complete  observe the resulting INSERT statements appear in a new query window  Use Find  amp  Replace to change all  temp table name  to  your table name   drop table  temp table name

User · Answer

You can use an INSERT INTO SELECT statement  to insert the results of a select query into a table  http   www w3schools com sql sql insert into select asp  Example   INSERT INTO Customers  CustomerName  Country  SELECT SupplierName  Country FROM Suppliers WHERE Country  Germany

User · Answer

You can Use Sql Server Integration Service Packages specifically designed for Import and Export operation    VS has a package for developing these packages if your fully install Sql Server   Integration Services in Business Intelligence Development Studio

User · Answer

Native method    for example if you have table   Users Id  name    You can do this   select  insert into Table values Id     Id      name     name       from Users

User · Answer

I created the following procedure   if object id  tool create insert    P   is null begin   exec  create procedure tool create insert as    end  go  alter procedure tool create insert  schema    varchar 200     dbo                                       table     varchar 200                                       where     varchar max    null                                      top       int   null                                      insert    varchar max  output  as begin   declare  insert fields varchar max              select        varchar max              error         varchar 500              query         varchar max      declare  values table description varchar max       set nocount on     -- Get columns   select  insert fields   isnull  insert fields               c name            select   case type name c system type id                        when  varchar  then isnull  select                           isnull            cast     c name     as varchar                null                           when  datetime  then isnull  select                           isnull            convert varchar      c name      121                null                           else isnull  select                          isnull cast     c name     as varchar     null                         end     from sys columns c with nolock           inner join sys tables t with nolock  on t object id   c object id          inner join sys schemas s with nolock  on s schema id   t schema id    where s name    schema      and t name    table     -- If there s no columns      if  insert fields is null or  select is null   begin     set  error    There  s no      schema          table     inside the target database        raiserror  error  16  1       return    end     set  insert fields    insert into      schema          table          insert fields           if isnull  where       lt  gt     and charindex  where   ltrim rtrim  where     lt  1   begin     set  where    where      where    end   else   begin     set  where         end     set  query    select     isnull  top     cast  top as varchar                select     from      schema          table     with  nolock       where     insert into  values description    exec  query      set  insert   isnull  insert   char 10          --    upper  schema          table      select  insert    insert   char 10     insert fields   char 10     values     v description          char 10     go    char 10      from  values v    where isnull v description       lt  gt      end  go   Then you can use it that way   declare  insert varchar max            part   varchar max            start  int           end    int   set  start   1   exec tool create insert  schema    dbo                            table    customer                            where     id   1                            insert    insert output   -- Print one line to avoid the maximum 8000 characters problem while len  insert   gt  0 begin   set  end   charindex char 10    insert      if  end   0   begin     set  end   len  insert    1    end     print substring  insert   start   end - 1     set  insert   substring  insert   end   1  len  insert  -  end   1   end    The output would be something like that   --DBO CUSTOMER insert into dbo customer id  name  type  values 1   CUSTOMER NAME    F    go   If you just want to get a range of rows  use the  top parameter as bellow   declare  insert varchar max            part   varchar max            start  int           end    int   set  start   1   exec tool create insert  schema    dbo                            table    customer                            top      100                           insert    insert output   -- Print one line to avoid the maximum 8000 characters problem while len  insert   gt  0 begin   set  end   charindex char 10    insert      if  end   0   begin     set  end   len  insert    1    end     print substring  insert   start   end - 1     set  insert   substring  insert   end   1  len  insert  -  end   1   end

User · Answer

You can Choose  Result to File  option in SSMS and export your select result to file and make your changes in result file and finally using BCP - Bulk copy you can insert in table 1 in database 2   I think for bulk insert you have to convert  rpt file to  csv file  Hope it will help

User · Answer

I think its also possible with adhoc queries  you can export result to excel file and then import that file into your datatable object or use it as it is  and then import the excel file into the second database  have a look at this link  this can help u alot   http   vscontrols blogspot com 2010 09 import-and-export-excel-to-sql-server html

User · Answer

This is a more versatile solution  that can do a little more than the question asks   and can be used in a query window without having to create a new stored proc - useful in production databases for instance where you don t have write access   To use the code  please modify according to the in line comments which explain its usage  You can then just run this query in a query window and it will print the INSERT statements you require   SET NOCOUNT ON  -- Set the ID you wish to filter on here DECLARE  id AS INT   123  DECLARE  tables TABLE  Name NVARCHAR 128   IdField NVARCHAR 128   IdInsert BIT  Excluded NVARCHAR 128    -- Add any tables you wish to generate INSERT statements for here  The fields are as thus   -- Name  Your table name  -- IdField  The field on which to filter the dataset  -- IdInsert  If the primary key field is to be included in the INSERT statement  -- Excluded  Any fields you do not wish to include in the INSERT statement INSERT INTO  tables  Name  IdField  IdInsert  Excluded  VALUES   MyTable1    Id   0   Created Modified   INSERT INTO  tables  Name  IdField  IdInsert  Excluded  VALUES   MyTable2    Id   1   Created Modified    DECLARE  numberTypes TABLE  sysId TINYINT   -- This will ensure INT and BIT types are not surrounded with quotes in the -- resultant INSERT statement  but you may need to add more  from sys types  INSERT  numberTypes SysId  VALUES 56   104   DECLARE  rows INT    SELECT COUNT    FROM  tables  DECLARE  cnt INT   1  DECLARE  results TABLE  Sql NVARCHAR 4000    WHILE  cnt  lt    rows BEGIN     DECLARE  tablename AS NVARCHAR 128      DECLARE  idField AS NVARCHAR 128      DECLARE  idInsert AS BIT     DECLARE  excluded AS NVARCHAR 128      SELECT          tablename   Name           idField   IdField           idInsert   IdInsert           excluded   Excluded     FROM  SELECT    ROW NUMBER   OVER ORDER BY  SELECT 1   AS RowId FROM  tables  t WHERE t RowId    cnt      DECLARE  excludedFields TABLE  FieldName NVARCHAR 128       DECLARE  xml AS XML   CAST    lt X gt     REPLACE  excluded         lt  X gt  lt X gt        lt  X gt    AS XML      INSERT INTO  excludedFields SELECT N value       NVARCHAR 128    FROM  xml nodes  X   AS T N       DECLARE  setIdentity NVARCHAR 128     SET IDENTITY INSERT      tablename      DECLARE  execsql AS NVARCHAR 4000     SELECT       CASE WHEN  idInsert   1 THEN  setIdentity     ON    CHAR 13  ELSE    END    INSERT INTO      tablename            SELECT  execsql    execsql       STUFF                             SELECT CASE WHEN NOT EXISTS SELECT   FROM  excludedFields WHERE FieldName   name  THEN        name ELSE    END             FROM sys columns             WHERE object id   OBJECT ID  dbo      tablename              FOR XML PATH                1  2                       CHAR 13     VALUES          STUFF                             SELECT                 CASE WHEN NOT EXISTS SELECT   FROM  excludedFields WHERE FieldName   name  THEN                               ISNULL                         CASE WHEN EXISTS SELECT   FROM  numberTypes WHERE SysId   system type id  THEN    ELSE               END                        CAST     name     AS VARCHAR                         CASE WHEN EXISTS SELECT   FROM  numberTypes WHERE SysId   system type id  THEN    ELSE               END                            NULL                        ELSE                    END             FROM sys columns             WHERE object id   OBJECT ID  dbo      tablename              FOR XML PATH                1  3                         CASE WHEN  idInsert   1 THEN CHAR 13     setIdentity     OFF  ELSE    END           FROM      tablename     WHERE      idField           CAST  id AS VARCHAR       INSERT  results EXEC   execsql      DELETE  excludedFields     SET  cnt    cnt   1 END  DECLARE cur CURSOR FOR SELECT Sql FROM  results OPEN cur  DECLARE  sql NVARCHAR 4000  FETCH NEXT FROM cur INTO  sql WHILE   FETCH STATUS   0 BEGIN     PRINT  sql     FETCH NEXT FROM cur INTO  sql END  CLOSE cur DEALLOCATE cur

User · Answer

If you are using Oracle  or configure the application to the SQL Server  then Oracle SQL Developer does this for you   choose  unload  for a table and follow the options through  untick DDL if you don t want all the table create stuff

User · Answer

I found this SMSMS Boost addon  which is free and does exactly this among other things  You can right click on the results and select Script data as

User · Answer

You can use this Q2C SSMSPlugin  which is free and open source  You can right click and select  Execute Query To Command    -  Query To Insert      Enjoy

User · Answer

SSMS Toolpack  which is FREE as in beer  has a variety of great features - including generating INSERT statements from tables   Update  for SQL Server Management Studio 2012  and newer   SSMS Toolpack is no longer free  but requires a modest licensing fee

User · Answer

1- Explanation of Scripts  A Syntax for inserting data in table is as below  Insert into table col1 col2 col3 col4 col5                    -- To achieve this part i                  --have used below variable                 ------ CSV COLUMN-------  values Col1 data in quote  Col2  quote   Col5  quote   -- To achieve this part  -- i e column data in  --quote i have used  --below variable ---- QUOTED DATA---   C To get above data from existing    table we have to write the select    query in such way that the output    will be in form of as above scripts  D Then Finally i have Concatenated    above variable to create    final script that s will    generate insert script on execution  E    TEXT  SELECT   INSERT INTO     TABLE NAME      CSV COLUMN   VALUES         SUBSTRING  QUOTED DATA 1 LEN  QUOTED DATA -5                Insert Scripts FROM    TABLE NAME    FILTER CONDITION   F And Finally Executed the above query EXECUTE TEXT   G QUOTENAME   function is used to wrap    column data inside quote  H ISNULL is used because if any row has NULL    data for any column the query fails    and return NULL thats why to avoid    that i have used ISNULL   I And created the sp sp generate insertscripts    for same  1- Just put the table name for which you want insert script  2- Filter condition if you want specific results  ----------Final Procedure To generate Script------  CREATE PROCEDURE sp generate insertscripts        TABLE NAME VARCHAR MAX        FILTER CONDITION VARCHAR MAX       AS BEGIN  SET NOCOUNT ON  DECLARE  CSV COLUMN VARCHAR MAX            QUOTED DATA VARCHAR MAX            TEXT VARCHAR MAX   SELECT  CSV COLUMN STUFF              SELECT       NAME      FROM sys all columns       WHERE OBJECT ID OBJECT ID  TABLE NAME  AND       is identity  1 FOR XML PATH           1 1       SELECT  QUOTED DATA STUFF              SELECT   ISNULL QUOTENAME   NAME     QUOTENAME                      NULL                  FROM sys all columns       WHERE OBJECT ID OBJECT ID  TABLE NAME  AND       is identity  1 FOR XML PATH           1 1       SELECT  TEXT  SELECT   INSERT INTO    TABLE NAME      CSV COLUMN   VALUES         SUBSTRING  QUOTED DATA 1 LEN  QUOTED DATA -5                Insert Scripts FROM    TABLE NAME    FILTER CONDITION  --SELECT  CSV COLUMN AS CSV COLUMN  QUOTED DATA AS QUOTED DATA  TEXT TEXT  EXECUTE   TEXT   SET NOCOUNT OFF  END

User · Answer

It s possible to do via Visual Studio SQL Server Object Explorer   You can click  View Data  from context menu for necessary table  filter results and save result as script

User · Answer

In SSMS    Right click on the database   Tasks   Generate Scripts Next Select  Select specific database objects  and check the table you want scripted  Next Click Advanced  gt  in the list of options  scroll down to the bottom and look for the  Types of data to script  and change it to  Data Only    OK Select  Save to new query window    Next   Next   Finish   All 180 rows now written as 180 insert statements

User · Answer

I had a similar problem  but I needed to be able to create an INSERT statement from a query  with filters etc     So I created following procedure   CREATE PROCEDURE dbo ConvertQueryToInsert   input NVARCHAR max    target NVARCHAR max   AS BEGIN      DECLARE  fields NVARCHAR max       DECLARE  select NVARCHAR max        -- Get the defintion from sys columns and assemble a string with the fields transformations for the dynamic query     SELECT          fields   COALESCE  fields                     name                 select   COALESCE  select                         ISNULL CAST      name      AS NVARCHAR max      NULL                FROM tempdb sys columns      WHERE  object id    OBJECT ID N tempdb     input        -- Run the a dynamic query with the fields from  select into a new temp table     CREATE TABLE  ConvertQueryToInsertTemp  strings nvarchar max       DECLARE  stmt NVARCHAR max     INSERT INTO  ConvertQueryToInsertTemp SELECT       select       AS  strings  FROM    input     exec sp executesql  stmt      -- Output the final insert statement      SELECT  INSERT INTO      target           fields      VALUES      REPLACE strings     NULL      NULL        FROM  ConvertQueryToInsertTemp      -- Clean up temp tables     DROP TABLE  ConvertQueryToInsertTemp     SET  stmt    DROP TABLE      input     exec sp executesql  stmt END   You can then use it by writing the output of your query into a temp table and running the procedure   -- Example table CREATE TABLE Dummy  Id INT  Comment NVARCHAR 50   TimeStamp DATETIME  INSERT INTO Dummy VALUES  1    Foo   GetDate      2   Bar   GetDate      3   Foo Bar   GetDate     -- Run query and procedure SELECT   INTO  TempTableForConvert FROM Dummy WHERE Id  lt  3 EXEC dbo ConvertQueryToInsert   TempTableForConvert    dbo Dummy    Note  This procedure only casts the values to a string which can cause the data to look a bit different  With DATETIME for example the seconds will be lost

User · Answer

Create a separate table using into statement  For example  Select   into Test 123 from  dbo   Employee  where Name like   Test    Go to the Database Right Click the Database Click on Generate Script Select your table Select advanace option and select the Attribute  Data Only  Select the file  open in new query   Sql will generate script for you

User · Answer

Using visual studio  do the following   Create a project of type  SQL Server-- SQL Server Database Project  open the sql server explorer  CTL-    CTL-S    add a SQL Server by right clicking on the SQL SERVER icon  Selcet ADD NEW SERVER  navigate down to the table you are interested in   right click--  VIEW DATA  Click the top left cell to highlight everything  ctl-A doesnt seem to work   Right Click -- SCript   This is fabulous  I have tried everything listed above over the years  I know there is a tool out there that will do this and much more  cant think of the name of it  But it is very expensive    Good luck  I just figured this out  Have not tested it extensively w  text fields etc  but it looks like it gets you a long ways down the road    Greg

[sql] Converting Select results into Insert script - SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to ssms