?
Solved

Auto Increment ID

Posted on 2009-04-08
9
Medium Priority
?
1,012 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

800 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