troubleshooting Question

OUTPUT only INSERTED identity value

Avatar of dbaSQL
dbaSQLFlag for United States of America asked on
Microsoft SQL Server 2008
3 Comments1 Solution269 ViewsLast Modified:
tableA     userID IDENTITY(1,1), username, windowsusername
tableB     acctID, username, windowsusername


insert into tableA is only username, windowsusername, and the userID is auto-generated.
this is done, works just fine.  i am revising the insert into tableB --
tableB inserts need to check tableA first, for @windowsusername
if exists, the @acctid is derived from tableA.userID, and used as tableB.acctID.
if not exists, the username and windowsusename are used in the insert to tableA, and i then need to OUTPUT the just-generated userID, and use as tableB.acctID in the insert.

I'm just having a little hang up with my OUTPUT INSERTED of the newly generated userid into my tableB.acctID.

any suggestions are appreciated.



IF EXISTS(SELECT 1 FROM dbo.TableA a WHERE a.WindowsUserName = @WindowsUserName)
BEGIN
DECLARE @acctID INT
SET @AcctID = (SELECT UserID FROM dbo.TableA a WHERE a.WindowsUserName = @WindowsUserName)
                  
      INSERT dbo.TableB (
            AcctID,UserName,WindowsUserName      )
      VALUES (
            @AcctID,@UserName,@WindowsUserName      )
      END
      ELSE
      BEGIN
      DECLARE @NewAcctID INT
      INSERT dbo.TableA (
            UserName,
            WindowsUserName
            )
      OUTPUT INSERTED.AcctID INTO @NewAcctID
      VALUES      (
              @UserName,
            @WindowsUserName
            )

      INSERT dbo.TableB
            (
            AcctID,
            UserName,
            WindowsUserName
            )
      VALUES (
            @NewAcctID,
            @UserName,
            @WindowsUserName
            );
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros