Insert Into Inner Joins 3

[trenttc]

The select works fine. Adding the insert into causes it to hang. How do I get the results into a new table?

INSERT INTO table3 (
id,lastn,firstn,addr3,state)

SELECT t1.id,t1.lastn,t1.firstn,addr3,t1.state
FROM cust t1
INNER JOIN(
SELECT lastn+firstn+state+addr3 AS dupmatch FROM cust
GROUP BY lastn+firstn+state+addr3
HAVING(COUNT(*) > 1)) AS t2
ON t1.lastn+t1.firstn+t1.state+t1.addr3 = t2.dupmatch
WHERE t1.country='000'
AND t1.email='';

 

[gmmastros]

If the table does not exist...

SELECT t1.id,t1.lastn,t1.firstn,addr3,t1.state
[!]Into   NewTable[/!]
FROM cust t1
INNER JOIN(
  SELECT lastn+firstn+state+addr3 AS dupmatch FROM cust
  GROUP BY lastn+firstn+state+addr3
  HAVING(COUNT(*) > 1)) AS t2
ON t1.lastn+t1.firstn+t1.state+t1.addr3 = t2.dupmatch   
WHERE t1.country='000'
AND t1.email='';

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jmeckley]

i think it's hanging because the query is most likely ineffecient. your looking for duplicates. try this instead

insert into duplicate_customer(id)
select c.id
from   cust c inner join
       (
          select min(id) as original_id, listn, firstn, state, addr3
          from   cust
          group by listn, firstn, state, addr3
          having  count(1) > 1
       ) t on c.id <> t.original_id and c.lastn = t.lastn and c.firstn = t.lastn and c.state = t.state and c.addr3 = t.addrr
where  c.email = '' and c.country = '000'

this will get a list of all the ids of duplicate records. this will not reference the orginal instance (smallest id.).

now if can reference the duplicate ids and assiciated values like this:

select c.* from duplicate_customer d inner join cust c on d.id = c.id
or
select * from cust where id in (select id from duplicate_customer)

there are those, much smarter than me, which may be able to solve this even more effeceintly. I would think all the concatination would create a poor execution path. of course my attempt may as well.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mrdenny]

Writing data to a table can take longer than just the select statement. This is because the insert must be logged then written to the table. Also if other users are using the table you are inserting into there will be locks in place on the table and you may have to wait for those locks to be released before you can do your insert.

The SELECT INTO method usually isn't recommended as it causes locking of system objects for the duration of the SELECT statement running. It's always better the create the table first using the CREATE TABLE command.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog