March 11, 2003 at 11:03 am
I have a table (call it NewData) that gets updated periodically from another table (call it OldData). The the OldData table has a column of INT that is used as the PK (call it CityData_SK). The value of this column was originally set as a IDENTITY column. However, since we can never renumber and we distribute the table to several data warehouse servers, we had to turn off IDENTITY after the initial load. The table has 135000 city names in it. I have to add 13000 new rows without disturbing the existing PK values. The new values are identified by doing a LEFT OUTER JOIN on CITY and STATE columns from NewData to OldData where OldData has NULLs in CityData_SK.
The problem is how can I set the CityData_SK to an incrementing value in an insert from statement? I don't think it can be done but I figured I'd pose the question first.
I poor solution I've come up with is to find the MAX value of the CityData_SK in OldData. Create a temp table with an identity column the has a seed value of MAX(CityData_SK) plus 1. Insert into the temp table from NewData, then insert into OldData from NewData. Not very elegant...
Edited by - tpantazi on 03/11/2003 2:17:33 PM
March 14, 2003 at 8:00 am
This was removed by the editor as SPAM
March 14, 2003 at 1:13 pm
quote:
I poor solution I've come up with is to find the MAX value of the CityData_SK in OldData. Create a temp table with an identity column the has a seed value of MAX(CityData_SK) plus 1. Insert into the temp table from NewData, then insert into OldData from NewData. Not very elegant...
Actually, I think that's a fine solution.
Edited by - jpipes on 03/17/2003 06:49:31 AM
March 17, 2003 at 7:52 am
How about this:
1) Turn on identity for that column.
2) Do your inserts (Will start doing IDs' from your MAX INT that currently exists in your column)
3) Turn off identity 🙂
Does that work?
H.Lindgren
P.S Maybe interesting reading:
In BOL search for 'SET IDENTITY_INSERT' in the 'SQL Programmer: Transact-SQL' section...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply