Inserting data into a Table with no Identity Column

Question

Inserting data into a Table with no Identity Column

Paul_Harvey

Hall of Fame

Points: 3822
More actions
May 4, 2010 at 7:27 am

#386845

I am using a SSIS package to synchronise data between my data warehouse main tableset, and an application database.
The application database has no identity column, but the application itself maintains an identity.
I need to insert new records, incrementing the ID column by 1 for each record. I have been scratching my head on this I'm afraid. I'd really appreciate a bit of advice.
Currently I am amending the database structure to make the column an Identity in SQL Management Studio manually before running the package. However I do need to change this back, because the application itself fail when I do this!
Paul

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

You are writing data from a DW back into an application database and want to mimic the application's IDENTITY logic?

Leaving aside the fact that the requirement sounds a little strange, do you have access to the program's API? If you could do the INSERTs using the application's own logic, that would be best.

If not, are you sure that it's just a case of getting the next available integer? Are there any related tables that also need to be populated or updated? You need to be confident that you are not blowing the integrity of the app db.

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 2

Hi Phil,

Yes it does sound a little strange I know. Its a long story, but this is an application that is being used from now on as a "read only" system from now on, as this was the system that was mostly responsible for creating all our duplicates and rogue data.

There were various application integrity issues that had to be overcome, as you have suggested, but these are in hand. There were other tables etc that had to be linked to the imported records. All of this has been done.

The DW sync is being done to update records and insert new ones on a daily basis.

Unfortunately, and unsurprisingly, that vendor is not allowing us access to the API.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Paul_Harvey (5/4/2010)
I am using a SSIS package to synchronise data between my data warehouse main tableset, and an application database.
The application database has no identity column, but the application itself maintains an identity.
I need to insert new records, incrementing the ID column by 1 for each record. I have been scratching my head on this I'm afraid. I'd really appreciate a bit of advice.
Currently I am amending the database structure to make the column an Identity in SQL Management Studio manually before running the package. However I do need to change this back, because the application itself fail when I do this!
Paul

Write the data to a temp table and use ROW_NUMBER() OVER to simulate the IDENTITY (with the correct offset, of course). Then use that temp table to do the update on the app.

Before you go charging off, though... you really need to find out how the app is maintaining reference to the "next ID". If it's using a "sequence" table, you'll need to find out where that table is so you can update it as well or the app will break.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)