CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
mathB
mathB

Reputation: 634

Copy certain data (not all) from one DB to another DB

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!

Upvotes: 0

Views: 57

Answers (1)

user170442
user170442

Reputation:

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

Upvotes: 2

Related Questions