Get new Identity Value Insert into another table

Question

Get new Identity Value Insert into another table

Attopeu

Ten Centuries

Points: 1308
More actions
December 8, 2010 at 10:25 am

#230180

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Attopeu Ten Centuries Points: 1308 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

GSquared SSC Guru Points: 260824 More actions · Answer 4

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

Lowell SSC Guru Points: 323507 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003927 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65138 More actions · Answer 7

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.