0

So, I know we can generate the scripts using Tasks--> Generate Scripts to copy over one DB to another DB, but what if I don't want all data? Say, I have a query which displays the only data I want from source - how can I copy the data and table from source to destination?

I need to restrict the data because the source table is huge.

My DB is: SQL Server 2008 (Source and Destination). Please help!

jarlh
  • 42,561
  • 8
  • 45
  • 63
mathB
  • 634
  • 8
  • 21
  • Duplicate of http://stackoverflow.com/a/25339437/6492765 ? Maybe this solution I linked can help you. You just write your own query with the WHERE statement. – MK_ Mar 08 '17 at 09:12

1 Answers1

2

You could create transfer semi-manually. Considering both databases are on the same server you could create stored procedure with something like this:

// Create heap table based on existing table
SELECT * INTO newdb.dbo.mytablecopy FROM olddb.dbo.myoriginaltable WHERE 1=2;

// Good idea to create some kind of id here and make it clustered index...
ALTER TABLE newdb.dbo.mytablecopy 
  ADD COLUMN id INT IDEINTITY(1,1) NOT NULL, 
  ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (id);

// Then actually transfer data
INSERT INTO newdb.dbo.mytablecopy(...provide list of columns...)
SELECT * FROM olddb.dbo.myoriginaltable WHERE ...provide your selection criteria...