Duplicate data with Identiy and FK

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 2008 Forums

Transact-SQL (2008)

Duplicate data with Identiy and FK

Author

Topic

halcyon1234
Starting Member

2 Posts

Posted - 2011-08-15 : 16:19:08

I have data that has a primary key, a year, and a child table. (The actual structure's more complex, but this is the jist of what I'm trying to do). I'd like to copy all the data from one year to the next, but am having issues with the while table.

If I had a structure like this:

tbl_parent
(
id int identity not null primary key,
year int,
title varchar(100)
)

tbl_child
(
id int, -- is fk to tbl_parent(id)
title varchar(100)
)

It's easy enough to copy the parent table

INSERT INTO tbl_parent (year, title) SELECT year + 1, title FROM tbl_parent WHERE year=@YearToCopy

But then I can't do the same for tbl_child, because there's no way of mapping the new id to the old id.

I've thought of using a cursor (or a loop in C#) to loop through each record in tbl_parent, insert it one at a time into tbl_parent, then use @@IDENTITY to copy the appropriate children over. But it feels wrong, and that there should be a better way?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-15 : 16:32:17

This seems like a pretty silly design to duplicate data every year, but I can think of instances where it makes sense.

Does this pull what you need?

SELECT	p2.id, p2.title
FROM	child c
JOIN	parent p1
On	c.id = p1.id
JOIN	parent p2
On	p1.title = p2.title
And	p1.year = p2.year - 1

halcyon1234
Starting Member

2 Posts

Posted - 2011-08-16 : 09:25:07

The main use is the client will create a list of questions that will be answered over the course of a year. The next year, they want to ask a new set of questions, but a significant number of the questions will be the same. So rather than having them type in all the questions again-- "Copy from Previous Year".

I'll take a look at the data to see if the 'title' fields are unique. If so, then yes, your solution will work perfectly. Thank you.

quote:
Originally posted by russell

This seems like a pretty silly design to duplicate data every year, but I can think of instances where it makes sense.

Does this pull what you need?
SELECT p2.id, p2.title
FROM child c
JOIN parent p1
On c.id = p1.id
JOIN parent p2
On p1.title = p2.title
And p1.year = p2.year - 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:24:59

you can combine both inserts like this


INSERT INTO tbl_child
SELECT ID,Title
FROM
(
INSERT INTO tbl_parent (year,title)
OUTPUT inserted.id,inserted.title
SELECT year + 1, title FROM tbl_parent WHERE year=@YearToCopy 
)t (ID,Title)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-17 : 10:09:59

Good call Visakh. I should've thought of that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources