Solved

Need to perform a DB Insert then return an ID?

Posted on 2014-01-17
11
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks guys...that worked!
0
 
LVL 65

Expert Comment

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

Author Comment

by:BlakeMcKenna
Comment Utility
Jim,

Your answer was correct. Since there were 2 correct answers, I accepted multiple answers.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
How do I reopen a ?
0
 

Author Comment

by:BlakeMcKenna
Comment Utility
Sorry...I'll redo it!
0
 
LVL 65

Expert Comment

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

Author Comment

by:BlakeMcKenna
Comment Utility
Your welcome...and thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

11 Experts available now in Live!

Get 1:1 Help Now