Very Computer

by **farlo_d..** » Fri, 18 Oct 2002 04:28:32

say I have a table (Name, Address, City, State, Zip) with a Unique
Index on (Address, City, State, Zip). Is there a way to do an

Insert Into Table1 Select * from Table2

which will only insert the ones that don't violate the unique
constraint.

the only thing I've run into is the statement quitting as soon as it
hits a violation. I'd like it to skip that row and move to the next
one.

Thanks

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

by oj » Fri, 18 Oct 2002 04:48:06

Insert Into Table1 Select * from Table2 t2
where not exists(select 1
from table1 t1
where t1.address=t2.address
and t1.city=t2.city
and t1.state=t2.state
and t1.zip=t2.zip)

--
-oj
http://www.rac4sql.net

Quote:> say I have a table (Name, Address, City, State, Zip) with a Unique
> Index on (Address, City, State, Zip). Is there a way to do an

> Insert Into Table1 Select * from Table2

> which will only insert the ones that don't violate the unique
> constraint.

> the only thing I've run into is the statement quitting as soon as it
> hits a violation. I'd like it to skip that row and move to the next
> one.

> Thanks

> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers

=-----

by **Robert Carneg** » Fri, 18 Oct 2002 19:07:17

If Table2 contains duplicates of its own rows, you could do

Insert Into Table1
Select MAX(Name), Address, City, State, Zip from Table2 t2
where not exists(select 1
from table1 t1
where t1.address=t2.address
and t1.city=t2.city
and t1.state=t2.state
and t1.zip=t2.zip)
group by address, city, state, zip

> Insert Into Table1 Select * from Table2 t2
> where not exists(select 1
> from table1 t1
> where t1.address=t2.address
> and t1.city=t2.city
> and t1.state=t2.state
> and t1.zip=t2.zip)

> --
> -oj
> http://www.rac4sql.net

> > say I have a table (Name, Address, City, State, Zip) with a Unique
> > Index on (Address, City, State, Zip). Is there a way to do an

> > Insert Into Table1 Select * from Table2

> > which will only insert the ones that don't violate the unique
> > constraint.

> > the only thing I've run into is the statement quitting as soon as it
> > hits a violation. I'd like it to skip that row and move to the next
> > one.

> > Thanks

> > -----------== Posted via Newsfeed.Com - Uncensored Usenet News
> ==----------
> > http://www.newsfeed.com The #1 Newsgroup Service in the World!
> > -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
> =-----

Very Computer

Import Data dropping duplicates

Import Data dropping duplicates

Import Data dropping duplicates

Import Data dropping duplicates