September 22, 2016 at 9:00 am
I am currently writing an SSIS package to migrate data from one third party database to a new third party one. This new third party uses a key in their tables of letter then 9 numeric.
I have created a staging table to move the data from the old database to first, to allow me to update some of the values (eg, country code in old database is UK, in new database is GB), and would like to be able to create the key in this and then insert into the new database.
Is there an easy way to do this?
Thanks 🙂
September 22, 2016 at 9:29 am
CharlieRL (9/22/2016)
I am currently writing an SSIS package to migrate data from one third party database to a new third party one. This new third party uses a key in their tables of letter then 9 numeric.I have created a staging table to move the data from the old database to first, to allow me to update some of the values (eg, country code in old database is UK, in new database is GB), and would like to be able to create the key in this and then insert into the new database.
Is there an easy way to do this?
Thanks 🙂
Make a new IDENTITY column in the table and create the 'key' as a calculated, persisted column, based on the IDENTITY column you've added. Here's an example. You may not need the NUMERIC_ROUNDABORT line ... depends on your settings.
SET NUMERIC_ROUNDABORT OFF;
IF OBJECT_ID('tempdb..#KeyTest', 'U') IS NOT NULL
DROP TABLE #KeyTest;
CREATE TABLE #KeyTest
(
KeyTestId INT IDENTITY(1, 1)
NOT NULL
,NewKey AS 'X' + RIGHT('00000000' + CAST(KeyTestId AS VARCHAR(9)), 9) PERSISTED
,SomeDesc VARCHAR(50) NOT NULL
);
INSERT #KeyTest
(
SomeDesc
)
SELECT 'Some description'
UNION ALL
SELECT 'Some other description';
SELECT *
FROM #KeyTest kt;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 22, 2016 at 12:59 pm
Take a look at the SEQUENCE object. That's one way to have this without using IDENTITY. Plus, IDENTITY just using SEQUENCE under the covers anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply