Michael J. Swart

CREATE SCHEMA A;
GO
 
CREATE TABLE A.JUDGES
(
	JudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL UNIQUE,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE A.[EVENTS]
(
	EventId INT IDENTITY NOT NULL PRIMARY KEY,
	[Year] INT NOT NULL,
	Location NVARCHAR(100) NOT NULL,
	UNIQUE ([Year], Location)
);
 
CREATE TABLE A.ATHLETES
(
	AthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL UNIQUE,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE A.EVENT_JUDGES
(
	EventId INT NOT NULL
		REFERENCES A.[EVENTS](EventId),
	JudgeId INT NOT NULL
		REFERENCES A.JUDGES(JudgeId),
	PRIMARY KEY (EventId, JudgeId)
)
 
CREATE TABLE A.EVENT_ATHLETES
(
	EventId INT NOT NULL
		REFERENCES A.[EVENTS](EventId),
	AthleteId INT NOT NULL
		REFERENCES A.ATHLETES(AthleteId),
	PRIMARY KEY (EventId, AthleteId)
)
 
CREATE TABLE A.SCORES
(
	EventId INT NOT NULL,
	AthleteId INT NOT NULL,
	JudgeId INT NOT NULL,
	Score NUMERIC(3,2) NOT NULL,
	PRIMARY KEY (EventId, AthleteId, JudgeId), 
	FOREIGN KEY (EventId, AthleteId)
		REFERENCES A.EVENT_ATHLETES(EventId, AthleteId),
	FOREIGN KEY (EventId, JudgeId)
		REFERENCES A.EVENT_JUDGES(EventId, JudgeId)
)

CREATE SCHEMA B;
GO
 
CREATE TABLE B.JUDGES
(
	JudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.[EVENTS]
(
	EventId INT IDENTITY NOT NULL PRIMARY KEY,
	[Year] INT NOT NULL,
	Location NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.ATHLETES
(
	AthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	Name NVARCHAR(100) NOT NULL,
	Country NVARCHAR(100) NOT NULL
);
 
CREATE TABLE B.EVENT_JUDGES
(
	EventJudgeId INT IDENTITY NOT NULL PRIMARY KEY,
	EventId INT NOT NULL
		REFERENCES B.[EVENTS](EventId),
	JudgeId INT NOT NULL
		REFERENCES B.JUDGES(JudgeId)
)
 
CREATE TABLE B.EVENT_ATHLETES
(
	EventAthleteId INT IDENTITY NOT NULL PRIMARY KEY,
	EventId INT NOT NULL
		REFERENCES B.[EVENTS](EventId),
	AthleteId INT NOT NULL
		REFERENCES B.ATHLETES(AthleteId)
)
 
CREATE TABLE B.SCORES
(
	ScoreId INT IDENTITY NOT NULL PRIMARY KEY,
	EventAthleteId INT NOT NULL REFERENCES B.EVENT_ATHLETES(EventAthleteId),
	EventJudgeId INT NOT NULL REFERENCES B.EVENT_JUDGES(EventJudgeId),
	Score NUMERIC(3,2) NOT NULL
)

/* Copy whole event */
DECLARE @EventIdToCopy INT = 43;
DECLARE @NewEventId INT;
 
INSERT A.[EVENTS](Location, [Year])
SELECT Location, [Year]
FROM A.[EVENTS]
WHERE EventId = @EventIdToCopy;
 
SET @NewEventId = SCOPE_IDENTITY();
 
INSERT A.EVENT_ATHLETES (EventId, AthleteId)
SELECT @NewEventId, AthleteId
FROM A.EVENT_ATHLETES
WHERE EventId = @EventIdToCopy;
 
INSERT A.EVENT_JUDGES (EventId, JudgeId)
SELECT @NewEventId, JudgeId
FROM A.EVENT_JUDGES
WHERE EventId = @EventIdToCopy;
 
INSERT A.SCORES(EventId, AthleteId, JudgeId, Score)
SELECT @NewEventId, AthleteId, JudgeId, Score
FROM A.SCORES
WHERE EventId = @EventIdToCopy;
 
/* For schema B, this is difficult and hairy. I could write a sample solution but it's not instructive.
Solutions usually include temp tables, output clauses and a mess of joins. Or you could instantiate
them as objects in an application and persist them again. But it's not as simple as Schema A */