Generate insert script for selected records

Question

I have a table with the following data   Pk Id  ProductName           Fk CompanyId       Price ------------------------------------------------------ 1      AMX                   1                  10 00 2      ABC                   1                  11 00 3      APEX                  1                  12 00 4      AMX                   1                  10 00 5      ABC                   1                  11 00 6      APEX                  1                  12 00 7      AMX                   2                  10 00 8      ABC                   2                  11 00 9      APEX                  2                  12 00   I want to generate Insert script for migrating records whose Fk CompanyId is 1   There is an insert script option to generate script for all records but I want to filter some records to migrate to another database

User · Answer

You could create a view with your criteria and then export the view

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    myTable                            where     Fk CompanyId   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 MYTABLE insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 1   AMX   1  10 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 2   ABC   1  11 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 3   APEX   1  12 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 4   AMX   1  10 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 5   ABC   1  11 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 6   APEX   1  12 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 7   AMX   2  10 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 8   ABC   2  11 00   go  insert into dbo myTable Pk Id  ProductName  Fk CompanyId  Price  values 9   APEX   2  12 00   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    myTable                            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

If you are using Oracle SQL Developer then it would be  select   insert     from TABLE NAME where COLUMN NAME    VALUE     Run this as a script

User · Answer

If possible use Visual Studio  The Microsoft SQL Server Data Tools  SSDT  bring a built in functionality for this since the March 2014 release   Open Visual Studio Open  quot View quot     quot SQL Server Object Explorer quot  Add a connection to your Server Expand the relevant database Expand the  quot Tables quot  folder Right click on relevant table Select  quot View Data quot  from context menu In the new window  viewing the data use the  quot Sort and filter dataset quot  functionality in the tool bar to apply your filter  Note that this functionality is limited and you can t write explicit SQL queries  After you have applied your filter and see only the data you want  click on  quot Script quot  or  quot Script to file quot  in the tool bar Voil   - Here you have your insert script for your filtered data  Note  Be careful  the  quot View Data quot  window is just like SSMS  quot Edit Top 200 Rows quot - you can edit data right away  Tested with Visual Studio 2015 with Microsoft SQL Server Data Tools  SSDT  Version 14 0 60812 0 and Microsoft SQL Server 2012

User · Answer

I ended up doing this in 2 steps  Selected the records I want into a new table in the database then generated a SQL data only script in SSMS  I did find and replace on the generated script and removed the table

User · Answer

If you are using the SQL Management Studio  you can right click your DB name and select  Tasks   Import Export data and follow the wizard  one of the steps is called  Specify Table Copy or Query  where there is an option to write a query to specify the data to transfer  so you can simply specify the following query   select   from  Table  where Fk CompanyId   1

User · Answer

SELECT  INSERT SomeOtherDB dbo table column1 column2 etc     SELECT     CONVERT VARCHAR 12   Pk Id                        REPLACE ProductName                                 CONVERT VARCHAR 12   Fk CompanyId                 CONVERT VARCHAR 12   Price        FROM dbo unspecified table name WHERE Fk CompanyId   1

User · Answer

CREATE PROCEDURE sp generate insertscripts        TABLENAME VARCHAR MAX        FILTER CONDITION VARCHAR MAX       -- where TableId   5 or some value   AS BEGIN  SET NOCOUNT ON  DECLARE  TABLE NAME VARCHAR MAX            CSV COLUMN VARCHAR MAX            QUOTED DATA VARCHAR MAX            TEXT VARCHAR MAX            FILTER VARCHAR MAX    SET  TABLE NAME  TABLENAME  SELECT  FILTER  FILTER CONDITION  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  --SELECT  CSV COLUMN AS CSV COLUMN  QUOTED DATA AS QUOTED DATA  TEXT TEXT  EXECUTE   TEXT   SET NOCOUNT OFF  END

User · Answer

In SSMS execute your sql query  From the result window select all cells and copy the values  Goto below website and there you can paste the copied data and generate sql scripts  You can also save results of query from SSMS as CSV file and import the csv file in this website   http   www convertcsv com csv-to-sql htm

[sql] Generate insert script for selected records?

Examples related to sql

Examples related to database

Examples related to sql-server-2008