sql-server - Export data from Microsoft SQL Server using a query to target data

Question

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).

标签： sql-serversql-server-2016sql-scripts

Answer 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.