Search code examples
sqlsql-servert-sqldatabase-administration

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

Solution

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