26 October 2007

SQL Puzzle 7

/*
Well, it has been a while since I posted up a puzzle. I  blame this on the very unflattering picture of myself attached to the blog. This causes me a natural reluctance to put it in a position where people might see it! It is that and …ahem… the cat ate my homework …ahem… blog entry. Anyway, this puzzle has a couple of parts. Since I am short on ideas, the first part is the same as the second part just with one extra column.

The puzzle is very simple. You have to move as much of the data as you can from the source tables to the destination tables. There is one restriction though;  you can only use one insert statement in each part! Extra points are definitely awarded for a solution that works in both SQL Server 2000 and 2005. So, to summarize, one insert statement to move data from Part1Source -> Part1Dest, and one insert stament to move Part2Source -> Part2Dest.

Have fun!

Lionel

CREATE TABLE Part1Source
(
    a INT,
    b INT,
    
    PRIMARY KEY (a,b)
)
    
INSERT INTO Part1Source VALUES (1,1)
INSERT INTO Part1Source VALUES (1,2)
INSERT INTO Part1Source VALUES (2,3)
INSERT INTO Part1Source VALUES (7,2)
INSERT INTO Part1Source VALUES (2,4)
INSERT INTO Part1Source VALUES (5,5)
INSERT INTO Part1Source VALUES (5,1)
INSERT INTO Part1Source VALUES (5,3)
INSERT INTO Part1Source VALUES (9,0)
INSERT INTO Part1Source VALUES (11,2)

CREATE TABLE Part1Dest
(
    a INT UNIQUE,
    b INT UNIQUE,
    
    FOREIGN KEY (a,b)  REFERENCES Part1Source(a,b)    
)

———————— Part 2 ————————

CREATE TABLE Part2Source
(
    a INT,
    b INT,
    c INT,
    
    PRIMARY KEY (a,b,c)
)
    
    
INSERT INTO Part2Source VALUES (1,1,2)
INSERT INTO Part2Source VALUES (1,2,2)
INSERT INTO Part2Source VALUES (2,3,1)
INSERT INTO Part2Source VALUES (7,2,1)
INSERT INTO Part2Source VALUES (2,4,7)
INSERT INTO Part2Source VALUES (5,5,6)
INSERT INTO Part2Source VALUES (5,1,4)
INSERT INTO Part2Source VALUES (5,3,4)
INSERT INTO Part2Source VALUES (9,0,1)
INSERT INTO Part2Source VALUES (11,2,9)

CREATE TABLE Part2Dest
(
    a INT UNIQUE,
    b INT UNIQUE,
    c INT UNIQUE,
    
    FOREIGN KEY (a,b,c)  REFERENCES Part2Source(a,b,c)    
)

*/

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2887 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Related articles

Also in Blogs

Azure SQL Data Warehouse Lives!

Frequently when a new piece of tech that I’m excited about is launched, total nerd that I am, I’ll start quoting Colin Clive in, still the best, James Wale’s Frankenstein. It’s ALIVE! ALIVE! Well, time to get excited. On Monday, July 11, Azure SQL Data Warehouse moves from being in preview on Azure to a … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening