December 8, 2010 at 10:25 am
Hi Guru,
I have two tables (AddressInfo and PracticeInfo) and below are tables structure:
AddressInfo:
AdressID INT IDENTITY (NOT NULL)
Address varchar(60)
PracticeInfo:
practiceID INT NOT NULL,
AdressID INT NOT NULL
These table tables don't have relationship defined and AdressInfo records count has more than PracticeInfo. The business logic is, we pull data from source and insert into AddressInfo first then into PracticeInfo.
When inserting records into PracticeInfo, how can I write a query to get new AddressID from AddressInfo to insert into PracticeInfo if records not existed on PracticeInfo?
I tried RIGHT JOIN to AddressInfo but seems not working.
Thanks so much,
Attopeu
December 8, 2010 at 10:27 am
If you insert 1 row into AddressInfo and then use scope_identity(), it will give you the ID number you just inserted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 10:39 am
Hi,
This is not a row by row operation and I have to write query to join AddressInfo table to staging table. So, I don't think scope_identity will work here.
Thanks,
Attopeu
December 8, 2010 at 10:42 am
If it's more than one row at a time, you can use an Output Into clause to get all the IDs you just inserted.
Create a temp table, Output Into it, then use that to insert into the sub-table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 1:51 pm
As always, please ignore the rabid monkey in the corner. He makes a lot of noise, but he can't actually bite anyone through an Internet connection. He does seem to try, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 2:26 pm
As Gsquared implied, it is perfectly normal to encounter a situation where you have to insert both Parent and then Child information into two different tables...since this is the whole idea behind normalizing the data.
Any implication that because the data is in a bad format causes you to be a bad person is bull; don't pay any attention to that fluff.
here's an OUTPUT example from a different post; if you give us specifics, we can help you adapt teh model, but we need the DDL of the tables, etc.
hope this helps.
--a table to capture the new Id and also the associated data for reference.
DECLARE @ResultsFromInsert TABLE(MapImageID int,
geoX decimal(10,4),
GeoY decimal(10,4),
Addr varchar(100),
Addr2 varchar(100),
city varchar(100),
[state] varchar(2),
country varchar(20),
zip varchar(9),
mpMapImage varbinary(max))
Insert into dbo.Map_Image(geoX,GeoY,Addr,Addr2,city,[state],country,zip,mpMapImage)
--OUTPUT clause has access to the INSERTED and DELETED tables from the trigger!
OUTPUT
INSERTED.MapImageID, --the new identity
INSERTED.geoX,
INSERTED.GeoY,
INSERTED.Addr,
INSERTED.Addr2,
INSERTED.city,
INSERTED.[state],
INSERTED.country,
INSERTED.zip,
INSERTED.mpMapImage
INTO @ResultsFromInsert
SELECT 0.0,0.0,[Address],'',[City],[State],Country,Zip,mpMapImage
From dbo.Map_MapPointXRef
--now i have a table variable with the new ID so i can insert into some child tables.
insert into someothertable(MapImageID,othercolumns)
select MapImageID AS FK,othercolumns
FROM
@ResultsFromInsert
Lowell
December 11, 2010 at 2:56 pm
CELKO (12/8/2010)
Your approach is completely wrong. You are trying to build pointer chains using the count of physical insertion attempts as pointers instead of a real key. You are not an SQL Programmer yet!SQL Programmers look for industry standards and not @%$&*! pointer chains. Does your industry use a SAN (Standard Address Number) like the book trade? Did you ask your shippers (FedEx, UPS, DHL, etc) what they use? Do you know about 9-1-1 addresses?
Did you even research this question? No, that would be work and require professionalism, education in the client's industry and all those things that code monkeys hate.
You crammed an entire address into a single column instead of breaking out meaningful data elements into their own columns (street, city, state, postal code, etc.). Why?
You will get a stinky kludge on a news group or forum, but it will not help you or your client in the long run.
Pffft!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2010 at 2:49 am
As already stated we need more DDL but at a guess....
INSERT PracticeInfo (PracticeID,AdressID)
SELECT s.PracticeID,a.AdressID
FROM [Source] s
JOIN AddressInfo a ON a.Asdress=s.Address
WHERE NOT EXISTS (SELECT * FROM PracticeInfo p WHERE p.PracticeID=s.PracticeID AND p.AdressID=a.AdressID)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply