Get new Identity Value Insert into another table

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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!

  • 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)

  • 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