Solved

Need to perform a DB Insert then return an ID?

Posted on 2014-01-17
11
262 Views
Last Modified: 2014-01-20
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
Comment
Question by:BlakeMcKenna
  • 5
  • 4
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39789462
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 39789463
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
 

Author Comment

by:BlakeMcKenna
ID: 39789766
Thanks guys...that worked!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39789792
Okay, I'll bite.  How did my comment not correctly answer the question?
0
 

Author Comment

by:BlakeMcKenna
ID: 39789837
Jim,

Your answer was correct. Since there were 2 correct answers, I accepted multiple answers.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39789849
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
 

Author Comment

by:BlakeMcKenna
ID: 39791068
How do I reopen a ?
0
 

Author Comment

by:BlakeMcKenna
ID: 39794256
Sorry...I'll redo it!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39794266
Thanks for the split.  Good luck with your project.  -Jim
0
 

Author Comment

by:BlakeMcKenna
ID: 39794267
Your welcome...and thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
Concatenating multiple comments into one row 16 62
Merge Statement 3 40
Dimension table indexes 8 12
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now