Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-05-05
10
Medium Priority
?
219 Views
Last Modified: 2010-03-19
Hi,
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.

thanks,
Bruce Mutch
0
Comment
Question by:bmutch
10 Comments
 
LVL 4

Assisted Solution

by:davehilditch
davehilditch earned 60 total points
ID: 13936560
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.
0
 

Author Comment

by:bmutch
ID: 13936642
Thanks, but I'm a developer and don't have access to do the DBA stuff.

Bruce
0
 
LVL 13

Assisted Solution

by:ispaleny
ispaleny earned 120 total points
ID: 13936653
The fastest way is to detach the source database, copy MDF and LDF files and attach the source files and the copied ones.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:bmutch
ID: 13936820
ispaleny,
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)
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 60 total points
ID: 13937102
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.
0
 

Author Comment

by:bmutch
ID: 13937200
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.



 
 
0
 

Author Comment

by:bmutch
ID: 13937675
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
INSERT ALL
  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
INSERT ALL
  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
INSERT ALL
  WHEN SAL>10000 THEN
    INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  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
INSERT FIRST
  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)
  ELSE
    INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
    MAX(hire_date) HIREDATE
  FROM employees GROUP BY department_id;
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 120 total points
ID: 13938608
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.
0
 

Author Comment

by:bmutch
ID: 13939455
Okay thanks, any other ideas to cut down on the work process.

0
 

Author Comment

by:bmutch
ID: 14038117
I'll split all the points, disappointing that Sql Server doesn't have the Oracle functionality.
thanks
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 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