CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-administration
Nani
Nani

Reputation: 103

copy tables from Prod to Test in SQL server

I have 2 tables in my Prod that have about 1M records each i need to copy just these two tables to my test environment and while doing that i need to able to delete the records from a particular date range. Can i set up a JOB to do it or any other process because i need to do it every month. I am using Import\Export for now. Thanks in advance

Upvotes: 1

Views: 1030

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could use Linked Server:

INSERT INTO db_name.schema.table_name(col1, col2, ...)
SELECT col1, col2, ...
FROM prd_linked_server.db_name.schema.table_name
WHERE date_column BETWEEN ... AND ...;  -- some expressions that calculate range

Also there is no need for deleting anything, just select required subset of data. As a final step, set SQL Server Agent Job and schedule it to run every month.

EDIT:

Instead of Linked Server you could save Import/Export Wizard as SSIS package and schedule it.

Save SSIS Package (SQL Server Import and Export Wizard)

How to: Automate SSIS Package Execution by Using the SQL Server Agent

Still you don't to move entire table, in source instead of selecting table, use custom query.

Upvotes: 1

Related Questions