Duplicating rows in table it's children from ano

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)

Duplicating rows in table it's children from ano

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-10-08 : 16:39:49

I have a lesson section table
A lesson table and lesson content table
Lesson section table lessonid sectionid start and end dates
Lesson table lessonid lesson name etc
Lesson content table lessonid contentid contenttype etc
I want to duplicate all rows for lessons brooking to a particular section and related record to another section lessonid in lesson table is identity
I way to do that without using a vursor

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-10-08 : 16:45:37

Note the contentid in lesson contentid in lesson content table is identity
And lessonid in lessoncontent table is a foreign key pointing to lessonid in lesson table
I know that I could had the section I'd in lessons table and got rid of lessonsevtiom table nut we had this way because we were not going tobduplicate the contents on duplicating th lesson but they decided not to do that

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-09 : 12:43:38

store your sectionid in a variable as below


DECLARE @ParentSectionID int,@ChildSectionID int
SET @ParentSectionID = <your value whose lessions are to copied>,@ChildSectionID = <your sectionid for which you need to copy values>

INSERT INTO [lession section]
SELECT ls.lessionId,@ChildSectionID,ls.start,ls.end
FROM [lession section] ls
WHERE ls.sectionid = @ParentSectionID

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

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-10-09 : 17:03:27

This I know I want to insert identity records
and recall the new identity lessonid's to which it corresponds to old lesson ids
so as to insert lessoncontents of old lessons to new lessons
without using a cursor

My question is how to map the new lessonid to orginal lessonid

example

use try
go
DECLARE @InsertedRows TABLE (newLessonID INT, orglessonid int)

INSERT dbo.Child1 (LessonTitle, LessonDesc)
OUTPUT inserted.LessonID as newlessonid

INTO @InsertedRows
SELECT
LessonTitle,LessonDesc
FROM
Child1 AS I
inner join
Master1 as M
on
I.lessonid=M.lessonID
where SectionID=1

insert into Master1 (lessonid,sectionid)

(select newLessonID,2 from @InsertedRows)

example master 1
lessonid sectionid adjustdate scheduletime
1 1 0 NULL
2 1 1 NULL
3 1 1 NULL

Child1
Lessonid Lessontitle Lessondesc
1 Less1Sec1 Lesson1for section 1
2 Less2Sec1 Lesson 2 for Section 1
3 Less3Sec1 Lesson 3 for Section 1

anscestor1
LessonContentID LessonID ComponentID Comments
1 1 O_23 NULL
2 1 O_24 NULL
3 1 O_25 NULL
4 2 O_26 NULL
5 2 O_27 NULLafter running the previous sql statements

master1
lessonid sectionid adjustdate scheduletime
1 1 0 NULL
2 1 1 NULL
3 1 1 NULL
4 2 0 NULL
5 2 0 NULL
6 2 0 NULL

child1
Lessonid Lessontitle Lessondesc
1 Less1Sec1 Lesson1for section 1
2 Less2Sec1 Lesson 2 for Section 1
3 Less3Sec1 Lesson 3 for Section 1
4 Less1Sec1 Lesson1for section 1
5 Less2Sec1 Lesson 2 for Section 1
6 Less3Sec1 Lesson 3 for Section 1

I want to have in the insertedrows table
a map between
orginal lessonid new lessonid
1 4
2 5
3 6

so as to continue to populate ancestor1
with the corresponding records

thanks a lot
sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 01:35:10

do as an update.


UPDATE ir
SET ir.orglessonid = c1.orglessonid
FROM @Inserted_rows ir
INNER JOIN (
SELECT MIN(lessonid) as orglessonid,MAX(lessonid) as newlessonid,Lessontitle,Lessondesc
FROM Child1 
GROUP BY Lessontitle,Lessondesc
)c1
ON c1.newlessonid = ir.newLessonid

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources