INSERT/SELECT - using 3 columns to get unique entry

>> Is there any way I can do a SELECT DISTINCT with three columns? <<

INSERT INTO tnOrder (col1, col2...ColN)
SELECT DISTINCT Col1,Col2... ColN etc
FROM tnOrderLine

>> Is there any way I can do a SELECT DISTINCT with three columns? <<

Not easily or directly ... unless the table has a unique column such as IDENTITY(), then it's fairly easy.

@rafranciso: yeah, but only three columns build the key - I also want to insert columns that aren't among those three columns building the key

@ScottPletcher: tnOrderlines has an ID column, so that each row is unique. The number of orders equals "SELECT DISTINCT COUNT (COl1,col2,col3)" from tnOrderlines.

Try this method:

CREATE TABLE #OrderTemp (
      Col1 int,
      Col2 varchar(10),
      Col3 bit,
      ...
      ColN char(5) )

INSERT INTO #OrderTemp (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM tnOrderLine
GROUP BY Col1, Col2, Col3

Update #OrderTemp
Set Col4 = ?, ..., ColN = ?
FROM tnOrderLine ol inner join #OrderTemp ot
      ON ot.Col1 = ol.Col1 and
      ot.Col2 = ol.Col2 and
      ot.Col3 = ol.Col3
WHERE
      <whatever>
      

INSERT INTO tnOrder
SELECT * from #OrderTemp

Drop table #OrderTemp


hth,
Landy

@ScottPletcher I get ambiguos column name col1, col2, col3

@Landy: What comes in where <whatever>?

Great, thanks! I actually added tnOrderLine. , on all columns except those in the first SELECT statement, since I didn't believe those could be ambigous... :)

The original 125pts for the question to Steve, rest to you Landy.

cheers,

henrik