1

I am trying to figure out how I would get the firstname and lastname from the INSERT statement that I just used. It created the row, and I want to use the First Name and the Last Name to use in the next insert statement, How would I go about doing this?

Declare @idnt int INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) VALUES ('Richard','Burns',NULL); SELECT @idnt = SCOPE_IDENTITY() INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner) VALUES (@idnt, '12/13/2011 10:55AM', 'Contact ' + @fname + " " + @lname + ", was automatically added by the system', 'Contact was automatically added by this system', 'System')
Improve this question

3 Answers 3

Reset to default
4

Use output!

INSERT INTO dbo.Activity ( ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner ) SELECT ContactID, GETDATE(), 'Contact ' + ContactFirstName + ' ' + ContactLastName, 'Contact was added by the system.', 'System' FROM ( INSERT INTO dbo.Contacts (ContactFirstName,ContactLastName,ContactCompany) OUTPUT inserted.ContactID, inserted.ContactFirstName, inserted.ContactLastName VALUES ('Richard','Burns',NULL) ) x

For more on the magic that is output, see here.

You can also use a trigger on the table to make it automagic rather than having to insert it manually each time. It uses the inserted/deleted tables, as well.

You would do that with this:

CREATE TRIGGER Contact_LogActivity ON dbo.Contacts FOR INSERT AS INSERT INTO dbo.Activity ( ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner ) SELECT ContactID, GETDATE(), 'Contact ' + ContactFirstName + ' ' + ContactLastName, 'Contact was added by the system.', 'System' FROM Inserted

Now, whenever any row gets inserted into Contacts, it logs it into Activity. For more on triggers, see here.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 I was going to mention OUTPUT... I was 10 minutes too late!
0

Using the identity caught by SCOPE_IDENTITY(), query back the table and grab the ContactFirstName and ContactLastName into your declared variables: @fname and @lname respectively:

... SELECT @idnt = SCOPE_IDENTITY() SELECT @fname = ContactFirstName , @lname = ContactLastName FROM dbo.Contacts WHERE YOUR_IDENTITY_COLUMN_HERE = @idnt INSERT INTO dbo.Activity ...

Replace YOUR_IDENTITY_COLUMN_HERE with the name of the identity column from which you assumed to grab SCOPE_IDENTITY() from and test..

Improve this answer

4 Comments

The only reason I don't like SCOPE_IDENTITY is that it may not be accurate--it's susceptible to a race condition where you insert a record, and another user inserts a record before your SCOPE_IDENTITY runs, and you get the wrong ID as a result of it. That can be very hard to identify as a bug when you see it happen in production.
@Eric: I don't think this would be the case - after all, it's called scope identity.... it will give the last inserted identity in your scope - so if some other user on a different connection inserts a new row - that won't affect your ID being returned... see MSDN docs on SCOPE_IDENTITY. IDENT_CURRENT(tablename) is not limited by scope - but always just returns that last ID inserted into that table specified
@marc_s: Comment redacted! I still like output better for readability reasons, but you're correct in that I was confusing scope_identity's behavior with that of @@IDENTITY.
@Eric: agreed - OUTPUT is a great if underused feature.
0

I think you are looking for an insert with a select statement. Try this:

 INSERT INTO dbo.Activity (ContactID, ActivityDate, ActivityName, ActivityNote, ActivityOwner) SELECT @idnt, '12/13/2011 10:55AM', 'Contact ' + ContactFirstName + ' ' + ContactLastName + ', was automatically added by the system', 'Contact was automatically added by this system', 'System' FROM Contacts WHERE ContactId = @idnt
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.