• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Need to perform a DB Insert then return an ID?

I need to write a Stored Procedure that will Insert a record into a Table and then return the Primary Key and I'm not sure how to do that. I'm using SQL Server 2008.
0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 5
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CREATE PROC sp_foo AS

INSERT INTO Customer(fk_customer_type_id, name)
VALUES (1, 'Jack Wagon')

-- Return the identity field generated by the above insert
SELECT SCOPE_IDENTITY()

GO

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if your table has an identity column and is the PK ,  perform the insert first and use SCOPE_IDENTITY() to get the id

INSERT into ...
SELECT SCOPE_IDENTITY()
0
 
BlakeMcKennaAuthor Commented:
Thanks guys...that worked!
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, I'll bite.  How did my comment not correctly answer the question?
0
 
BlakeMcKennaAuthor Commented:
Jim,

Your answer was correct. Since there were 2 correct answers, I accepted multiple answers.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm showing that only the second answer was accepted, so if your intent was to split you'll need to re-open the question and re-award points.
0
 
BlakeMcKennaAuthor Commented:
How do I reopen a ?
0
 
BlakeMcKennaAuthor Commented:
Sorry...I'll redo it!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
 
BlakeMcKennaAuthor Commented:
Your welcome...and thanks!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now