Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Auto Increment ID

Posted on 2009-04-08
9
Medium Priority
?
1,014 Views
Last Modified: 2013-11-10
Hi,
I'm using SSIS 2005 and I need to increment a value in the package as a source column for use as the primary key field in the destination insert. I also would like to find out the maximum key used in the destination table already so that I can set my counter for this field at the value +1. NB. A restriction is that I can't change the destination PK field to an identity field.
0
Comment
Question by:geoffcoles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Assisted Solution

by:bmatumbura
bmatumbura earned 840 total points
ID: 24096985
Create a SQL function that you can call when inserting the data. The function should return MAX(<id_column>) + 1
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 600 total points
ID: 24097034
Hello geoffcoles,

Why not simply set the ID field as an identity column with an increment of 1?

Regards,

Patrick
0
 
LVL 11

Assisted Solution

by:bmatumbura
bmatumbura earned 840 total points
ID: 24097037
The function:-

Replace [Your_Table] and ID_IN_Table with the values you are using for the Tablename and the IDColumn
CREATE  FUNCTION [fn_GetNextID]()
RETURNS INTEGER
AS
BEGIN
DECLARE @Return NVARCHAR(20)
SELECT @Return = (SELECT CAST(Max(ID_IN_Table) AS INTEGER) + 1 AS [NewSeq] FROM [Your_Table])
RETURN (@Return)
END

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 560 total points
ID: 24097143
using such a function is not really good, as it WILL fail with multi-use access.
as matthewspatrick indicats, using identity is exactly made for that.

to retrieve the generated ID after the insert:
SELECT SCOPE_IDENTIY()

Open in new window

0
 
LVL 11

Assisted Solution

by:bmatumbura
bmatumbura earned 840 total points
ID: 24097381
But the user says "A restriction is that I can't change the destination PK field to an identity field..."
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 560 total points
ID: 24097620
yes, I read that.
still, I "have" to post that it's not good.
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 600 total points
ID: 24102398
geoffcoles,

I admit, I missed the bit where you said you cannot use identity.  That said, like angelIII I still think that your best bet
by far is to use identity.  No other method will work nearly as well.

Regards,

Patrick
0
 

Author Closing Comment

by:geoffcoles
ID: 31568016
Thank you for all your assistance. I have decided to change my ID field to an identity column. This has led to other design changes in our application but it's definitely for the best. Thanks for all your help.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24137346
good to hear that we could convince you :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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