1

I know how to generate scripts to script insert lines allowing me to backup some data. I was wondering though if it was possible to write a query (using WHERE clause as an example) to target a very small subset of data in a very large table?

In the end I want to generate a script that has a bunch of insert lines and will allow for inserting primary key values (where it normally would not let you).

Arvo Bowen
  • 4,524
  • 6
  • 51
  • 109
  • 2
    What are you actually asking here? if you know how to generate a script for all the rows, why not just use a subset of those, or alter the process you're using to generate the script for all the rows. You haven't told us *how* you're doing that so how can we explain how to change that process? – Thom A Sep 04 '19 at 13:41
  • 1
    Sorry, I thought there was only one way to "generate scripts". The only way I know how to to it in `MS SQL Server Management Studio` is `Tasks`>`Generate Scripts`. As far as I can tell there is no option to "use a subset of those". Regardless of how I was doing it though my final goal (last sentence) is the same and what I need guidance on. Thanks! – Arvo Bowen Sep 04 '19 at 14:15

2 Answers2

1

If I read your requirement correctly, what you actually want to do is simply make a copy of some data in your table. I typically do this by using a SELECT INTO. This will also generate the target table for you.

CREATE TABLE myTable (Column1 int, column2 NVARCHAR(50))
;
INSERT INTO myTable VALUES (1, 'abc'), (2, 'bcd'), (3, 'cde'), (4, 'def')
;
SELECT * FROM myTable
;
SELECT 
    *
INTO myTable2
FROM myTable WHERE Column1 > 2
;

SELECT * FROM myTable;
SELECT * FROM myTable2;

DROP TABLE myTable;
DROP TABLE myTable2;

myTable will contain the following:

Column1 column2
1   abc
2   bcd
3   cde
4   def

myTable2 will only have the last 2 rows:

Column1 column2
3   cde
4   def

Edit: Just saw the bit about the Primary Key values. Does this mean you want to insert the data into an existing table, rather than just creating a backup set? If so, you can issue SET IDENTITY_INSERT myTable2 ON to allow for this.

However, be aware that might cause issues in case the id values you are trying to insert already exist.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
  • Thanks SchmitzIT, to answer your last question (`SET IDENTITY_INSERT myTable2 ON`), that's exactly what I was talking about. I'm going to be using this to restore data from one database to another. The src DB is an old backup and the dest is a DB that was altered by mistake. So the IDs are not going to be an issue at all as they no longer exist in the dest DB. As for the (what I consider a) work around to do what I'm trying to do I was hoping there was an easy way to just use the normal method of creating a script but adding a `WHERE` to the SRC results. Thanks for the solution though! – Arvo Bowen Sep 04 '19 at 15:16
  • @ArvoBowen Would the destination table need the IDs, though? Typically the purpose of an identity column is to guarantee a unique way to identify a record. An ID by itself shouldn't be a means to add any form of business logic (that's what natural keys would be for). – SchmitzIT Sep 05 '19 at 07:28
  • In most cases the IDs are also used for linking data from other tables. Such as is my case. The IDs are very important in my case. – Arvo Bowen Sep 05 '19 at 11:46
  • @ArvoBowen - That's fair:) – SchmitzIT Sep 05 '19 at 14:24
1

SSMS will not let you to have the INSERT queries for specific rows in a table. You can do this by using GenerateInsert stored procedure. For example :

EXECUTE dbo.GenerateInsert @ObjectName = N'YourTableName'
,@SearchCondition='[ColumnName]=ColumnValue';

will give you similar result for the filtered rows specified in the @SearchCondition

Let's say your table name is Table1 which has columns Salary & Name and you want the insert queries for those who have salary greater than 1000 whose name starts with Mr., then you can use this :

EXECUTE dbo.GenerateInsert @ObjectName = N'Table1'
,@SearchCondition='[Salary]>1000 AND [Name] LIKE ''Mr.%'''
,@PopulateIdentityColumn=1;
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
  • I'll give this a shot and see if it does what I'm after, thanks MD. Although your example seems confusing. I'm not sure what I might put in the variable `@SearchCondition`. Could you give em and example of something that might be used? Thanks! – Arvo Bowen Sep 04 '19 at 15:18
  • Ahh!! Now I get it! I was expecting that to do something else. I guess that's the way you pass in variables to stored procedures. Learn something new every day. ;) – Arvo Bowen Sep 04 '19 at 15:32
  • One last question, can it handle multiple conditions? I have about 5 I need to apply to it. Will this work as an example? `EXECUTE dbo.GenerateInsert @ObjectName = N'Table1', @SearchCondition='[Salary]>1000 AND [Name] LIKE "Mr.%"';` For that matter, how would I use quotes? Is my double quote the correct method? – Arvo Bowen Sep 04 '19 at 15:38
  • 1
    Yes it supports multiple conditions like you did. For quote, don't use double quote, just add an additional single quote in each side like `AND [Name] LIKE ''Mr.%''` – Md. Suman Kabir Sep 04 '19 at 15:47
  • I always verify the answer works before I up vote (which I have already done) or mark it answered. I'll get you that green check mark as soon as I finish checking it out. ;) - One last thing. Per my original question I also required `inserting primary key values` just like what @SchmitzIT was talking about `SET IDENTITY_INSERT Table1 ON`. Can this also account for that? Currently it just ignores my primary key column. – Arvo Bowen Sep 04 '19 at 15:59
  • You can set value to another parameter for this `@PopulateIdentityColumn=1` follow the updated answer. Actually there are more options which might be useful, you can check them more as i didn't use them all when i used it! – Md. Suman Kabir Sep 04 '19 at 16:06