Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

INSERT/SELECT - using 3 columns to get unique entry

Posted on 2005-05-17
9
Medium Priority
?
221 Views
Last Modified: 2008-03-10
I want to create an Order table out of a table of Orderlines.

I thought of doing something like this (this statement is illegal)

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

How do I solve this? Is there any way I can do a SELECT DISTINCT with three columns?

best regards,

henrik
0
Comment
Question by:henrikatwork
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14019368
>> 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
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14019543
>> 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.
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 14022729
@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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 7

Expert Comment

by:LandyJ
ID: 14022781
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
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 14022869
INSERT INTO tnOrder (col1, col2...ColN)
SELECT Col1,Col2... ColN etc
FROM tnOrderLine
INNER JOIN (
    SELECT col1, col2, col3, MIN(id) AS id  --<<-- or MAX(), of course
    FROM tnOrderLine
    GROUP BY col1, col2, col3
) AS uniqOrder ON uniqOrder.col1 = tnOrderLine.col1 AND uniqOrder.col2 = tnOrderLine.col2 AND uniqOrder.col3 = tnOrderLine.col3 AND uniqOrder.id = tnOrderLine.id
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 14025673
@ScottPletcher I get ambiguos column name col1, col2, col3
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 14025701
@Landy: What comes in where <whatever>?
0
 
LVL 7

Accepted Solution

by:
LandyJ earned 400 total points
ID: 14027405
If you have any selection criteria for the records in tnOrderLine, put it here.  If you want all of the records, eliminate the WHERE clause.

(Scott, not trying to step on your answer.  I was here, so I thought I'd help out)
You need to qualify the columns in the SELECT clause.  SQL doesn't know if you mean Col1 from tnOrderLine or the alais uniqueOrder.  Even though they are physically the same table, logically, tnOrderLine and uniqueTable are individual tables.  Use:

SELECT tnOrderLine.Col1, tnOrderLine.Col2,...tnOrderLine.ColN

hth,
Landy
0
 
LVL 1

Author Comment

by:henrikatwork
ID: 14033985
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question