Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    23

    Unanswered: Need Query for update and insert in one go.

    Hi All,
    Please look at the following tables. I have Two tables
    Data and TmpData with the following structure.

    Data ( All int columns, ID column is Primary and identity)

    Id UserID PrgID RoldID
    322 1 1 2
    323 1 2 2
    324 1 3 2
    325 2 1 2
    326 2 2 2
    327 2 3 2
    328 3 1 2
    329 3 2 2
    330 3 3 2


    TmpData
    Id UserID PrgID RoldID
    82 1 1 3
    83 1 2 3
    84 1 3 3
    85 2 1 3
    86 2 2 3
    87 2 3 3
    91 20 1 2
    92 20 2 2
    93 20 3 2
    94 21 1 2
    95 21 2 2
    96 21 3 2


    Now I need to run a query so that
    Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
    Part2 : It inserts new rows in 'TmpData' to 'Data' table.

    I am done with Part1 using the simple update statement.

    Update Data
    Set Data.programroleid=tmp.ProgramRoleID
    from TmpData tmp
    where Data.userid=tmp.UserId
    and Data.programId=tmp.ProgramId

    This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.

  2. #2
    Join Date
    May 2006
    Posts
    16
    INSERT INTO Data(ID, UserID, PrgID, RoldID)
    SELECT * FROM TmpData
    WHERE ID not in (SELECT ID FROM TmpData Inner Join Data on TmpData.UserID=Data.UserID and TmpData.ProgID=Data.PrgID and TmpData.RoldID=Data.RoldID)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ID is auto-generated identity value, and thus cannot be inserted into the target table (unless you specifically disable this for the transaction...).
    I wouldn't expect the identity values to match between Data and TmpData anyway, so the "NOT IN" method is probably not appropriate. It is also not efficient. NOT EXISTS is faster than NOT IN.

    Code:
    INSERT INTO Data
    	(ID,
    	UserID,
    	PrgID,
    	RoldID)
    SELECT	ID,
    	UserID,
    	PrgID,
    	RoldID
    FROM	TmpData
    WHERE NOT EXISTS
    	(SELECT	*
    	FROM	Data
    	WHERE	Data.UserID = TmpData.UserID 
    			and Data.PrgID = TmpData.PrgID
    			and Data.RoldID = TmpData.RoldID)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •