Re: Query to split one table into 2
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 08/11/04
- Next message: Neil W.: "Re: how to tell if a column is part of a computed column?"
- Previous message: Peter The Spate: "simple query locking too much data"
- In reply to: M K: "Re: Query to split one table into 2"
- Next in thread: David Portas: "Re: Query to split one table into 2"
- Reply: David Portas: "Re: Query to split one table into 2"
- Reply: M K: "Re: Query to split one table into 2"
- Reply: M K: "Re: Query to split one table into 2"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 11 Aug 2004 14:25:51 +0100
> I am not asking design
> advice on the tables.
Unfortunately it's a design problem. Lack of keys makes many things in SQL
impossible or at least very difficult. That's why correct design is so
important.
Taking just the subset of columns you posted earlier, try this:
INSERT INTO Products (photographerid, sort, programname, packagename)
SELECT DISTINCT customer, sort, program, package
FROM OldData
INSERT INTO ProductPriceCodes (productid, gpcode1, gpcode2, gpcode3)
SELECT DISTINCT P.photographerid, O.code1, O.code2, O.code3
FROM OldData AS O
JOIN Products AS P
ON O.customer = P.photographerid
AND O.sort = P.sort
AND O.program = P.programname
AND O.package = P.packagename
There are problems with this. Maybe it happens that the same combination of
(photographerid, sort, programme, packagename) occurs for multiple products?
You can't resolve that in your design. Then again, maybe it never happens.
Also, because you don't have a natural key in the target table you can't
easily handle NULLs in the data - rows with NULLs just won't get inserted. I
guess you could try to work around this in the join of the above SELECT but
better to declare a proper key to start with (did I mention how important
keys are...?) :o)
-- David Portas SQL Server MVP --
- Next message: Neil W.: "Re: how to tell if a column is part of a computed column?"
- Previous message: Peter The Spate: "simple query locking too much data"
- In reply to: M K: "Re: Query to split one table into 2"
- Next in thread: David Portas: "Re: Query to split one table into 2"
- Reply: David Portas: "Re: Query to split one table into 2"
- Reply: M K: "Re: Query to split one table into 2"
- Reply: M K: "Re: Query to split one table into 2"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
