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
Solved

Auto Increment ID

Posted on 2009-04-08
9
1,008 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 143

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 143

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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24137346
good to hear that we could convince you :)
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 Lag Function With Group Levels 1 26
SQL Server / Update DB? 22 36
SQL Syntax Grouping Sum question 7 24
SQLCMD Output to file from xpcmd_shell 6 15
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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