Easiest way to copy date from multiple related tables to a test Database

Posted on 2005-05-05
Last Modified: 2010-03-19
Can anyone tell me how I can copy records from multiple tables in a DB to a test DB. Lets say I have data in 5 related tables, is there anyway to write a query to get this data from all the tables and then copy it to either their corresponding tables in the test DB or temp tables. If not, is there any faster way than either manually copying the records from/to each table or using "insert into" on each single table.

Bruce Mutch
Question by:bmutch
    LVL 4

    Assisted Solution

    Easiest method is to Backup the database then do a Restore to a differently named database.  Make sure you select Options and change the data and log file locations for the test database.

    Author Comment

    Thanks, but I'm a developer and don't have access to do the DBA stuff.

    LVL 13

    Assisted Solution

    The fastest way is to detach the source database, copy MDF and LDF files and attach the source files and the copied ones.

    Author Comment

    sorry I am not following your answer, don't know what MDF and LDF files are. By the way, I do want to be able to copy just certain records in the tables (with a select statement probably)
    LVL 21

    Assisted Solution

    If you don't have DBA rights to do backup/restore, DTS, etc.  then Insert...Select * is probably your best bet.  If you have large tables, this could take awhile and consume many system resources, so you may want to do it off-hours.

    Author Comment

    Thanks, but can you do some sort of insert into multiple tables, something like

    insert into T1(fld2,fld3) inner join T2(fld2,fld3) on T1.field1 = T2.field2
    values (select ...)

    Anything but doing each individual insert into on each table.


    Author Comment

    I found the following for Oracle, anything similar in T-SQL?:

    Multitable Inserts
    Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format:

    -- Unconditional insert into ALL tables
      INTO sal_history VALUES(empid,hiredate,sal)
      INTO mgr_history VALUES(empid,mgr,sysdate)
    SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
      FROM employees WHERE employee_id > 200;

    -- Pivoting insert to split non-relational data
      NTO Sales_info VALUES (employee_id,week_id,sales_MON)
      INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
      INTO Sales_info VALUES (employee_id,week_id,sales_WED)
      INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
      NTO Sales_info VALUES (employee_id,week_id, sales_FRI)
    SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
        sales_WED, sales_THUR,sales_FRI
    FROM Sales_source_data;

    -- Conditionally insert into ALL tables
      WHEN SAL>10000 THEN
      WHEN MGR>200 THEN
        INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
    SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
      FROM employees WHERE employee_id > 200;

    -- Insert into the FIRST table with a matching condition
      WHEN SAL > 25000  THEN
        INTO special_sal VALUES(DEPTID,SAL)
      WHEN HIREDATE like ('%00%') THEN
        INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
      WHEN HIREDATE like ('%99%') THEN
        INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
        INTO hiredate_history VALUES(DEPTID, HIREDATE)
    SELECT department_id DEPTID, SUM(salary) SAL,
        MAX(hire_date) HIREDATE
      FROM employees GROUP BY department_id;
    LVL 13

    Accepted Solution

    You can do the Oracle functionality in specialised ETL tools, for example DataStage. SQL Server 2000 is not able to insert into 2 tables in one insert.

    Author Comment

    Okay thanks, any other ideas to cut down on the work process.


    Author Comment

    I'll split all the points, disappointing that Sql Server doesn't have the Oracle functionality.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now