insert into + join question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert into + join question

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-31 : 09:46:34

declare @errors table (

erroriD varchar (20),
errorMessage varchar (100))

insert @errors select '1', 'unable to resolve address'
----------------------------------------------------------------------------------------------------------------------------------------

declare @addressLookup table (

address varchar (100))

insert @addressLookup
select'water'union all
select'apple'
----------------------------------------------------------------------------------------------------------------------------------------

declare @exception table (

address varchar (100),
errorID varchar (100))
----------------------------------------------------------------------------------------------------------------------------------------

declare @address table (
addressName varchar (100)
)

insert @address
select 'route 50 st'union all
select 'pine heaven st'union all
select 'banana st' union all
select 'apple st' union all
select 'sand st'

----------------------------------------------------------------------------------------------------------------------------------------

insert @exception (address, errorID)
select substring(a.addressName, 1, charindex(' ', a.addressName)), e.errorID
from @address a, @errors e
where substring(a.addressName, 1, charindex(' ', a.addressName)) not in
(select address from @addresslookup al)
and e.errorID ='1'

select * from @exception
----------------------------------------------------------------------

If the addressName is not in the @addressLookUp table. This addressName should be insert into the @exception table with the correct errorID from @errors.
i wrote the code but 'Pine heaven' and 'route 50 'is not showing.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-31 : 09:58:24

I get pine, route, banana, sand
when I run it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gongxia649
So Suave

344 Posts

Posted - 2006-08-31 : 10:11:06

i fixed it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 10:16:33

quote:
Originally posted by gongxia649

i fixed it.

I am impressed. Good work!

Peter Larsson
Helsingborg, Sweden

gongxia649
So Suave

344 Posts

Posted - 2006-09-01 : 15:09:44

declare @errors table (

erroriD varchar (20),
errorMessage varchar (100))

insert @errors select '1', 'unable to resolve address'
insert @errors select '2', 'wrong birthDATE'
----------------------------------------------------------------------------------------------------------------------------------------

declare @addressLookup table (

address varchar (100))

insert @addressLookup
select'water'union all
select'us route 103'
----------------------------------------------------------------------------------------------------------------------------------------

declare @exception table (

addressException varchar (100),
errorID varchar (100))
----------------------------------------------------------------------------------------------------------------------------------------

declare @address table (
addressName varchar (100)
)
insert @address
select 'route50 st street'union all
select 'sand circuit' union all
select 'water st'union all
select 'us route 103'union all
select '29 STREET'

----------------------------------------------------------------------------------------------------------------------------------------

insert @exception(addressException, errorID)
select CASE
when substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName))+1) not in (select address from @addresslookup al)
then substring(a.addressName, 1, len(a.addressName) - charindex(' ', reverse(a.addressName)))
end,
'1'
from @address a

select * from @exception

"us route 103" is in the @lookupTable. how do i write the code for it not to show up?

magnetica
Starting Member

36 Posts

Posted - 2006-09-01 : 20:24:18

Surely you just do a simple SELECT WHERE that field/s is not equal to "us route 103"!
If this is not what you meant then plz say?!

If you think you know it all?! You know nothing!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources