Solved

Auto Increment ID

Posted on 2009-04-08
9
1,007 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Assisted Solution

by:bmatumbura
bmatumbura earned 210 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 92

Accepted Solution

by:
Patrick Matthews earned 150 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 210 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 140 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 210 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 142

Assisted Solution

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 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 142

Expert Comment

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

Featured Post

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!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

776 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