How to duplicate linked table data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to duplicate linked table data

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 04:13:24

I have 4 database tables that represent an online questionnaire:


  Table1 holds the title of the questionnaire title and description
   ¦
   +- Table2 holds the sections where questions are placed
       ¦
       +- Table3 holds the questions
           ¦
           +- Table4 holds the answers that a user can choose from

Each table is linked by a single PK/FK relationship.

I need to devise a way to duplicate questionnaires. For example, I need to create a copy of all data in Table1, then reflect this down through the three foreign key tables and create duplicate rows for each. The idea being that an identical copy is created, but which is not dependant on the original questionnaire.

Is there an easy way to achieve this in SQL 2005? Currently I can only think of enumerating through each table which is going to be a tough one to write for my little brain...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 04:49:09

Are the Keys autonumber (identity)?

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 05:19:41

Hi webfred

Yes they are, for all tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 06:27:55

I have not the time to code it in this moment.
But I would do this:
Add a column to every table with name orig_id
insert table1 with select from table1 and store the original id in the added column
insert table2 with select from table2 and store the original id in the added column and join from table1 on t2.fk=t1.orig_id so that you get the new id as the now needed foreign key.

And so on.

If I am not clear enough then I hope another mate will proceed helping.

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 07:35:56

Hi, yes I think I see what you're saying. I'll give it a go with table variables to hold the data as the ID's are changed, to save modifying my actual database tables.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 08:30:15

quote:
Originally posted by webfred

insert table2 with select from table2 and store the original id in the added column and join from table1 on t2.fk=t1.orig_id so that you get the new id as the now needed foreign key.
And so on.

Okay its the 'And so on' bit which has caused me to stumble. In table 2, there are many rows that act as the foreign key for table 3, and likewise between table3 and table4. Therefore, I don't know how to get the new ID values for each row without inserting them one at once from the table variable...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 09:04:24

Wait a few minutes...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 09:31:08

Try like this:


set nocount on
-- create test tables
-- t1_id, t2_id, t3_id are the foreign keys
create table table1  (id int identity(1,1),title varchar(20))
create table table2  (id int identity(1,1),t1_id int not null,section varchar(20))
create table table3  (id int identity(1,1),t2_id int not null,question varchar(20))
create table table4  (id int identity(1,1),t3_id int not null,answer varchar(20))

-- create test data
insert table1
select 'T-SQL Questions' union all
select 'DBA Qestions'

insert table2
select 1,'Tricky Section' union all
select 1,'Simple Section' union all
select 2,'Maintaining'

insert table3
select 1,'How would you...' union all
select 1,'What is the...' union all
select 2,'The recovery model..' union all
select 3,'How to restore...'

insert table4
select 1,'Answer A' union all
select 1,'Answer B' union all
select 2,'Answer A' union all
select 2,'Answer B' union all
select 3,'Answer A' union all
select 3,'Answer B' union all
select 4,'Answer A' union all
select 4,'Answer B' 

select * from table1 t1
join table2 t2 on t1.id=t2.t1_id
join table3 t3 on t2.id=t3.t2_id
join table4 t4 on t3.id=t4.t3_id

-- result
--id          title                id          t1_id       section              id          t2_id       question             id          t3_id       answer
------------- -------------------- ----------- ----------- -------------------- ----------- ----------- -------------------- ----------- ----------- --------------------
--1           T-SQL Questions      1           1           Tricky Section       1           1           How would you...     1           1           Answer A
--1           T-SQL Questions      1           1           Tricky Section       1           1           How would you...     2           1           Answer B
--1           T-SQL Questions      1           1           Tricky Section       2           1           What is the...       3           2           Answer A
--1           T-SQL Questions      1           1           Tricky Section       2           1           What is the...       4           2           Answer B
--1           T-SQL Questions      2           1           Simple Section       3           2           The recovery model.. 5           3           Answer A
--1           T-SQL Questions      2           1           Simple Section       3           2           The recovery model.. 6           3           Answer B
--2           DBA Qestions         3           2           Maintaining          4           3           How to restore...    7           4           Answer A
--2           DBA Qestions         3           2           Maintaining          4           3           How to restore...    8           4           Answer B

-- --------------------------
-- Start work duplicating...
-- --------------------------

alter table table1 add old_id int
alter table table2 add old_id int
alter table table3 add old_id int


insert table1(title,old_id)
select title,id
from table1

insert table2(t1_id,section,old_id)
select t1.id,t2.section,t2.id
from table2 t2 join table1 t1 on t2.t1_id=t1.old_id

insert table3(t2_id,question,old_id)
select t2.id,t3.question,t3.id
from table3 t3 join table2 t2 on t3.t2_id=t2.old_id

insert table4(t3_id,answer)
select t3.id,t4.answer
from table4 t4 join table3 t3 on t4.t3_id=t3.old_id



-- last step would be to drop the added columns
alter table table1 drop column old_id
alter table table2 drop column old_id
alter table table3 drop column old_id


-- show the result
select * from table1 t1
join table2 t2 on t1.id=t2.t1_id
join table3 t3 on t2.id=t3.t2_id
join table4 t4 on t3.id=t4.t3_id

-- result
--id          title                id          t1_id       section              id          t2_id       question             id          t3_id       answer
------------- -------------------- ----------- ----------- -------------------- ----------- ----------- -------------------- ----------- ----------- --------------------
--1           T-SQL Questions      1           1           Tricky Section       1           1           How would you...     1           1           Answer A
--1           T-SQL Questions      1           1           Tricky Section       1           1           How would you...     2           1           Answer B
--1           T-SQL Questions      1           1           Tricky Section       2           1           What is the...       3           2           Answer A
--1           T-SQL Questions      1           1           Tricky Section       2           1           What is the...       4           2           Answer B
--1           T-SQL Questions      2           1           Simple Section       3           2           The recovery model.. 5           3           Answer A
--1           T-SQL Questions      2           1           Simple Section       3           2           The recovery model.. 6           3           Answer B
--2           DBA Qestions         3           2           Maintaining          4           3           How to restore...    7           4           Answer A
--2           DBA Qestions         3           2           Maintaining          4           3           How to restore...    8           4           Answer B
--3           T-SQL Questions      4           3           Tricky Section       5           4           How would you...     9           5           Answer A
--3           T-SQL Questions      4           3           Tricky Section       5           4           How would you...     10          5           Answer B
--3           T-SQL Questions      4           3           Tricky Section       6           4           What is the...       11          6           Answer A
--3           T-SQL Questions      4           3           Tricky Section       6           4           What is the...       12          6           Answer B
--3           T-SQL Questions      5           3           Simple Section       7           5           The recovery model.. 13          7           Answer A
--3           T-SQL Questions      5           3           Simple Section       7           5           The recovery model.. 14          7           Answer B
--4           DBA Qestions         6           4           Maintaining          8           6           How to restore...    15          8           Answer A
--4           DBA Qestions         6           4           Maintaining          8           6           How to restore...    16          8           Answer B

-- cleaning
drop table table1
drop table table2
drop table table3
drop table table4

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:25:12

If you stuck on adapting this to your tables then give us the table structure please.

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 10:36:51

Thanks for a very detailed explanation. I'm trying to copy your functionality into a stored procedure, but am getting errors:


   CREATE PROC myProc
   AS
   BEGIN TRAN
        ALTER TABLE table1 ADD old_id INT
        ' rest of alter statements...

        INSERT INTO table1 (title, old_id) SELECT title, id FROM table1 -- 'Invalid column name 'old_id'.'
        ' ....

Why is this being caused when the old_id column is added in the statement above? Is it because at run time the column does not exist? Is there a way around this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 11:19:01

Try:


EXEC('ALTER TABLE table1 ADD old_id INT;
      ALTER TABLE table2 ADD old_id INT;
      ALTER TABLE table3 ADD old_id INT')

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-08 : 11:34:14

The problem remains I'm afraid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-09 : 01:44:12

This is working:


create proc myProc
as
begin tran

IF not exists (select column_name from  INFORMATION_SCHEMA.columns where 
                    table_name = 'table1' and   column_name = 'old_id')
	alter table table1 add old_id int

IF not exists (select column_name from  INFORMATION_SCHEMA.columns where 
                    table_name = 'table2' and   column_name = 'old_id')
	alter table table2 add old_id int

IF not exists (select column_name from  INFORMATION_SCHEMA.columns where 
                    table_name = 'table3' and   column_name = 'old_id')
	alter table table3 add old_id int
	

exec('
insert table1(title,old_id)
select title,id
from table1;

insert table2(t1_id,section,old_id)
select t1.id,t2.section,t2.id
from table2 t2 join table1 t1 on t2.t1_id=t1.old_id;

insert table3(t2_id,question,old_id)
select t2.id,t3.question,t3.id
from table3 t3 join table2 t2 on t3.t2_id=t2.old_id;

insert table4(t3_id,answer)
select t3.id,t4.answer
from table4 t4 join table3 t3 on t4.t3_id=t3.old_id;



-- last step would be to drop the added columns
alter table table1 drop column old_id;
alter table table2 drop column old_id;
alter table table3 drop column old_id')
commit

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources