I am actually working in VB6.0 using the .SQL property of a querydef.

I would like to use a select..into statement to generate a new table
containing just the records that have multiple enteries in a given field
(netname). I am assuming it would be some form of a
SELECT * INTO newname
FROM TPL
WHERE (SELECT distinct netname FROM TPL ...)
and some form of a record count >1.  It seems it should be able to be done
in a single query statement, but it is eluding me as how to do it.

The other way I can think of is a round about method of copying the entire
table, use the results of a SELECT distinct into a recordset, .MoveFirst,
query again on that name and if recordcount=1 go back and delete that name
out of the copy of the table.  Seems to me that that is a very slow and
cumbersome way of doing it.

Can you post some ddl, sample data and expected result?


AMB

Show quoteHide quote

First, the entire database is generated in code off an ASCII file.
Here is a quick couple of lines:

11 - -15                  VIA0180   1    X   4.710 Y   0.230   T    
12 - -UP_A              C121        2    X   4.295 Y   4.725   T    S
12 - -UP_A              C164        2    X   4.295 Y   6.415   T    S
12 - -UP_A              C39          2    X   4.295 Y   1.345   T    S


Quick code snippet that chopped the first 75% of data off that I did not
need to store.

With qdfProbable
     .SQL = "SELECT * into Probable FROM TPL Where (TPL.SMT <> 'S' ) And
(TPL.Top = 'T' ) ORDER BY NETNUM"
     .Execute
     Set Rst = TPLdb.OpenRecordset("Probable", dbOpenDynaset)
End With
..Tabledefs.Refresh

Due to the data I am playing with, if there is only one distinct entry for a
given netname, it is the only place I could put a probe.  If there are
multiple for a given netname then I will need to reiterate through those
records to find out what I can make work.

For the above lines of data I would want returned:
(Note the above sample code snippet would remove all three of these lines as
they are surfacemount topside components)

12 - -UP_A                C121        2    X   4.295 Y   4.725   T    S
12 - -UP_A                C164        2    X   4.295 Y   6.415   T    S
12 - -UP_A                C39          2    X   4.295 Y   1.345   T    S

Show quoteHide quote

Peter, here are a couple of ways to achieve this,

SELECT * INTO newname
FROM TPL AS T1
WHERE EXISTS
  (SELECT * FROM TPL AS T2
   WHERE T2.netname = T1.netname
     AND T2.keycol <> T1.keycol)

SELECT * INTO newname
FROM TPL AS T
  JOIN (SELECT netname
        FROM TPL
        GROUP BY netname
        HAVING COUNT(*) > 1) AS G
  ON T.netname = G.netname

Show quoteHide quote

Select T0.* Into NewTable
From MyTable T0
  Inner Join
     (Select NetName From MyTable Group By NetName Having Count(*) > 1) as T1
  on T0.NetName = T1.NetName


Show quoteHide quote

You basically want to select from TPL where netname is included in a set of
namename values that have a group count of more than 1. If there are
millions of rows, and performance is this query is an issue, then you may
want to consider indexing on netname.

select * into newname
from tpl
where netname in
    (
    select netname
    from tpl
    group by netname
    having count(*) > 1
    )


Show quoteHide quote

Thank you all for your help.

Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess