Copying Related Data - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Copying Related Data

Author

Topic

ernie99
Starting Member

12 Posts

Posted - 2010-08-09 : 13:48:52

Hi everyone, I'm a bit stuck at the moment and would really appreciate your help with writing this query.

I am trying to create a copy of an Event Record (Event Table) and all data related to that record. I am having trouble figuring out how to copy session records (see diagram).

One event can have many streams. Many streams can have many sessions so the table SessionStream was created to resolve the many to many relationship.

I need to achieve the following.
Copy all Stream Records (duplicate the records within the table changing only the EventCode) from the old Event (@eventCode) to the New Event (@newEventCode).
Copy all Session Records (duplicate the records within the table) from the old Event to the New Event. The only way to know which Event a Session belongs to is to do a join on the SessionStream table.
Finally the SessionStream records need to be inserted so that the new StreamCodes are linked to the new SessionCodes.

So in summary I'm trying to copy a set of related data. I can't work out how to take the new identities that are created for the new Event data and link them correctly based on the old identities of the old Event data.

Hopefully that makes some kind of sense. Apologies if it doesn't. Here's the stored procedure I've created so far. Thanks in advance for your help.

ALTER       PROCEDURE [dbo].[EV_Event_Copy]
(
	@userCode int,
	@eventCode int,
	@eventName nvarchar(200)
)
AS
BEGIN
	BEGIN TRAN
		
		DECLARE @newEventCode int			
		
		INSERT INTO [EVENT]
			(EVENTNAME, VENUECODE, SECTORCODE, [DESCRIPTION], EMAIL, WEBSITE, [STATUS], 
			 STARTDATE, ENDDATE, CREATEDBY, ISCONFERENCE, ISEXHIBITION)
		SELECT @eventName, E.VENUECODE, E.SECTORCODE, E.DESCRIPTION, E.EMAIL, E.WEBSITE, E.STATUS, 
		E.STARTDATE, E.ENDDATE, @userCode, E.ISCONFERENCE, E.ISEXHIBITION
		FROM 
			[EVENT] E INNER JOIN VENUE V ON E.VENUECODE = V.VENUECODE
		WHERE 
			E.EVENTCODE = @eventCode
		--Get new event ID
		SELECT @newEventCode = CAST( IDENT_CURRENT('EVENT') AS int);
		--Create permission for event for this user
		INSERT CMS_USEREVENT(EVENTCODE, USERCODE, MAIN) 
		SELECT @newEventCode, @userCode, CASE WHEN EXISTS 
			(SELECT * FROM CMS_USEREVENT WHERE USERCODE = @userCode) 
		THEN 0 ELSE 1 END;
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 1
		END
		--Copy exhibitors
		INSERT INTO EVENTEXHIBITOR 
			(COMPANYCODE, EVENTCODE, PACKAGECODE, SUMMARY, PAGETEXT, FEATURED, CREATED,
			UPDATED, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE, DISPLAYEMAIL, DISPLAYWEBSITE,
			SELFSERVICEUPDATE)
		SELECT COMPANYCODE, @newEventCode, PACKAGECODE, SUMMARY, PAGETEXT, FEATURED, getDate(),
			getDate(), DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE, DISPLAYEMAIL, DISPLAYWEBSITE,
			SELFSERVICEUPDATE
		FROM EVENTEXHIBITOR WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 2
		END
		--Copy zones
		INSERT INTO ZONE
			(ROOMCODE, EVENTCODE, ZONENAME, [DESCRIPTION], PRIORITY)
		SELECT ROOMCODE, @newEventCode, ZONENAME, [DESCRIPTION], PRIORITY
		FROM ZONE WHERE EVENTCODE = @eventCode		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 3
		END
		--Copy stands
		INSERT INTO STAND
			(EVENTCODE, ZONECODE, [STATUS], STANDTYPECODE, COMPANYCODE, STAND, [DESCRIPTION],
			AREA, DIMENSION, PRICE, PRIORITY, CREATED, UPDATED)
		SELECT @newEventCode, ZONECODE, [STATUS], STANDTYPECODE, COMPANYCODE, STAND, [DESCRIPTION],
			AREA, DIMENSION, PRICE, PRIORITY, getdate(), getDate()
		FROM STAND WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 4
		END
		--Copy event sponsors
		INSERT INTO EVENTSPONSOR
			(COMPANYCODE, EVENTCODE, PACKAGECODE, [STATUS], SPONSORTYPECODE, SUMMARY, PAGETEXT,
			PRIORITY, FEATURED, CREATED, UPDATED, RATE, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE,
			DISPLAYEMAIL, DISPLAYWEBSITE, SELFSERVICEUPDATE)
		SELECT COMPANYCODE, @newEventCode, PACKAGECODE, [STATUS], SPONSORTYPECODE, SUMMARY, PAGETEXT,
			PRIORITY, FEATURED, getdate(), getDate(), RATE, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE,
			DISPLAYEMAIL, DISPLAYWEBSITE, SELFSERVICEUPDATE
		FROM EVENTSPONSOR WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 5
		END

		--Copy streams
		INSERT INTO STREAM
			(STREAMTYPECODE, EVENTCODE, STREAM, [DESCRIPTION], PRIORITY)
		SELECT STREAMTYPECODE, @newEventCode, STREAM, [DESCRIPTION], PRIORITY
		FROM STREAM WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 6
		END	

		--Insert magic here to copy session data

		-- Maybe use a temp table? perhaps use OUTPUT clause?
		--DECLARE @SESSIONSTREAM TABLE
			--(SESSIONCODE INT, STREAMCODE INT, ORIGSESSIONCODE INT, ORIGSTREAMCODE INT)

	COMMIT TRAN
END
RETURN 0

ernie99
Starting Member

12 Posts

Posted - 2010-08-10 : 09:22:52

This is how I did it. Specifically the section "copy session stream mappings".


ALTER       PROCEDURE [dbo].[EV_Event_Copy]
(
	@userCode int,
	@eventCode int,
	@eventName nvarchar(200)
)
AS
BEGIN
	BEGIN TRAN

		-- This can only work if there are no orphan sessions. This is a check for orphans.
		IF EXISTS (SELECT * FROM SESSION WHERE SESSIONCODE NOT IN (SELECT SESSIONCODE FROM SESSIONSTREAM))
			BEGIN
				ROLLBACK TRAN
				RETURN 1
			END
		
		DECLARE @newEventCode int			
		
		--Copy Event
		INSERT INTO [EVENT]
			(EVENTNAME, VENUECODE, SECTORCODE, [DESCRIPTION], EMAIL, WEBSITE, [STATUS], 
			 STARTDATE, ENDDATE, CREATEDBY, ISCONFERENCE, ISEXHIBITION)
		SELECT @eventName, E.VENUECODE, E.SECTORCODE, E.DESCRIPTION, E.EMAIL, E.WEBSITE, E.STATUS, 
		E.STARTDATE, E.ENDDATE, @userCode, E.ISCONFERENCE, E.ISEXHIBITION
		FROM 
			[EVENT] E INNER JOIN VENUE V ON E.VENUECODE = V.VENUECODE
		WHERE 
			E.EVENTCODE = @eventCode
		--Get new event ID
		SELECT @newEventCode = CAST( IDENT_CURRENT('EVENT') AS int);
		--Create permission for event for this user
		INSERT CMS_USEREVENT(EVENTCODE, USERCODE, MAIN) 
		SELECT @newEventCode, @userCode, CASE WHEN EXISTS 
			(SELECT * FROM CMS_USEREVENT WHERE USERCODE = @userCode) 
		THEN 0 ELSE 1 END;
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 2
		END
		--Copy exhibitors
		INSERT INTO EVENTEXHIBITOR 
			(COMPANYCODE, EVENTCODE, PACKAGECODE, SUMMARY, PAGETEXT, FEATURED, CREATED,
			UPDATED, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE, DISPLAYEMAIL, DISPLAYWEBSITE,
			SELFSERVICEUPDATE)
		SELECT COMPANYCODE, @newEventCode, PACKAGECODE, SUMMARY, PAGETEXT, FEATURED, getDate(),
			getDate(), DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE, DISPLAYEMAIL, DISPLAYWEBSITE,
			SELFSERVICEUPDATE
		FROM EVENTEXHIBITOR WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 3
		END
		--Copy zones
		INSERT INTO ZONE
			(ROOMCODE, EVENTCODE, ZONENAME, [DESCRIPTION], PRIORITY)
		SELECT ROOMCODE, @newEventCode, ZONENAME, [DESCRIPTION], PRIORITY
		FROM ZONE WHERE EVENTCODE = @eventCode		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 4
		END
		--Copy stands
		INSERT INTO STAND
			(EVENTCODE, ZONECODE, [STATUS], STANDTYPECODE, COMPANYCODE, STAND, [DESCRIPTION],
			AREA, DIMENSION, PRICE, PRIORITY, CREATED, UPDATED)
		SELECT @newEventCode, ZONECODE, [STATUS], STANDTYPECODE, COMPANYCODE, STAND, [DESCRIPTION],
			AREA, DIMENSION, PRICE, PRIORITY, getdate(), getDate()
		FROM STAND WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 5
		END
		--Copy event sponsors
		INSERT INTO EVENTSPONSOR
			(COMPANYCODE, EVENTCODE, PACKAGECODE, [STATUS], SPONSORTYPECODE, SUMMARY, PAGETEXT,
			PRIORITY, FEATURED, CREATED, UPDATED, RATE, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE,
			DISPLAYEMAIL, DISPLAYWEBSITE, SELFSERVICEUPDATE)
		SELECT COMPANYCODE, @newEventCode, PACKAGECODE, [STATUS], SPONSORTYPECODE, SUMMARY, PAGETEXT,
			PRIORITY, FEATURED, getdate(), getDate(), RATE, DISPLAYADDRESS, DISPLAYCOUNTRY, DISPLAYPHONE,
			DISPLAYEMAIL, DISPLAYWEBSITE, SELFSERVICEUPDATE
		FROM EVENTSPONSOR WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 6
		END
		--Copy streams
		INSERT INTO STREAM
			(STREAMTYPECODE, EVENTCODE, STREAM, [DESCRIPTION], PRIORITY)
		SELECT STREAMTYPECODE, @newEventCode, STREAM, [DESCRIPTION], PRIORITY
		FROM STREAM WHERE EVENTCODE = @eventCode
		ORDER BY STREAMCODE
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 7
		END
		-- Copy sessions
		INSERT INTO SESSION
			(SESSIONTYPECODE, BLOCKING, ROOMCODE, SESSIONNAME, [DESCRIPTION], STARTTIME, ENDTIME, MAXCAPACITY)
		SELECT SESSIONTYPECODE, BLOCKING, ROOMCODE, SESSIONNAME, [DESCRIPTION], STARTTIME, ENDTIME, MAXCAPACITY
		FROM SESSION WHERE SESSIONCODE IN (SELECT SESSIONCODE FROM SESSIONSTREAM SS 
												INNER JOIN STREAM S ON SS.STREAMCODE = S.STREAMCODE 
												WHERE EVENTCODE = @eventCode)
		ORDER BY SESSIONCODE
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 8
		END
		-- Copy session stream mappings
		DECLARE  @streamorderedlist TABLE ( id int, rownumber int )
		DECLARE  @streamneworderedlist TABLE ( id int, rownumber int )
		DECLARE  @sessionorderedlist TABLE ( id int, rownumber int )
		DECLARE  @sessionneworderedlist TABLE ( id int, rownumber int )

		INSERT INTO @streamorderedlist (id, rownumber)		
		SELECT streamcode, ROW_NUMBER()  OVER ( ORDER BY streamcode ) as rowid
		FROM stream
		WHERE eventcode = @eventcode

		INSERT INTO @streamneworderedlist (id, rownumber)		
		SELECT streamcode, ROW_NUMBER()  OVER ( ORDER BY streamcode ) as rowid
		FROM stream
		WHERE eventcode = @newEventCode
		
		INSERT INTO @sessionorderedlist (id, rownumber)		
		SELECT sessioncode, ROW_NUMBER()  OVER ( ORDER BY sessioncode ) as rowid
		FROM session		
		WHERE sessioncode in (SELECT SESSIONCODE FROM SESSIONSTREAM SS 
									INNER JOIN STREAM S ON SS.STREAMCODE = S.STREAMCODE 
									WHERE EVENTCODE = @eventCode)
		ORDER BY SESSIONCODE
		
		INSERT INTO @sessionneworderedlist (id, rownumber)		
		SELECT sessioncode, ROW_NUMBER()  OVER ( ORDER BY sessioncode ) as rowid
		FROM session s 		
		WHERE NOT EXISTS (select sessioncode from sessionstream ss where ss.sessioncode = s.sessioncode)
		ORDER BY S.SESSIONCODE

		INSERT INTO sessionstream (sessioncode, streamcode)
		SELECT NEWSESSION.ID, NEWSTREAM.ID
		FROM SESSIONSTREAM
		INNER JOIN @sessionorderedlist oldsession	ON SESSIONSTREAM.SESSIONCODE = oldsession.id
		INNER JOIN @sessionneworderedlist newsession ON oldsession.rownumber = newsession.rownumber
		LEFT OUTER JOIN @streamorderedlist oldstream ON oldstream.id = SESSIONSTREAM.STREAMCODE
		LEFT OUTER JOIN @streamneworderedlist newstream ON newstream.rownumber = oldstream.rownumber	
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 9
		END

		--Copy Event Days
		INSERT INTO EVENTDAY
			(EVENTCODE, EVENTDAY, EVENTDAYDESC, STARTTIME, ENDTIME)
		SELECT @newEventCode, EVENTDAY, EVENTDAYDESC, STARTTIME, ENDTIME
		FROM EVENTDAY WHERE EVENTCODE = @eventCode
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 10
		END

		-- Copy eventdaysession mappings
		DECLARE  @eventdayorderedlist TABLE ( id int, rownumber int )
		DECLARE  @eventdayneworderedlist TABLE ( id int, rownumber int )

		INSERT INTO @eventdayorderedlist (id, rownumber)		
		SELECT EVENTDAYCODE, ROW_NUMBER()  OVER ( ORDER BY EVENTDAYCODE ) as rowid
		FROM EVENTDAY
		WHERE eventCode = @eventcode

		INSERT INTO @eventdayneworderedlist (id, rownumber)		
		SELECT EVENTDAYCODE, ROW_NUMBER()  OVER ( ORDER BY EVENTDAYCODE ) as rowid
		FROM EVENTDAY
		WHERE eventCode = @newEventCode

		INSERT INTO EVENTDAYSESSION (EVENTDAYCODE, SESSIONCODE)
		SELECT NEWEVENTDAY.ID, NEWSESSION.ID
		FROM EVENTDAYSESSION
		INNER JOIN @sessionorderedlist oldsession	ON EVENTDAYSESSION.SESSIONCODE = oldsession.id
		INNER JOIN @sessionneworderedlist newsession ON oldsession.rownumber = newsession.rownumber
		LEFT OUTER JOIN @eventdayorderedlist oldeventday ON oldeventday.id = EVENTDAYSESSION.EVENTDAYCODE
		LEFT OUTER JOIN @eventdayneworderedlist neweventday ON neweventday.rownumber = oldeventday.rownumber	
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN
			RETURN 11
		END

		

	COMMIT TRAN
END
RETURN 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources