KEY_TABLE vs IDENTITY entity IDs

Hey guys...

I converted my SQL Server database to use a Key Table for the ID property, as I can see where it would make LightSpeed work better when inserting a bunch of records. Now I want to refactor one of my tables to pull out some common information so it can be re-used by another table, and I'm wondering how to make everybody happy.

I created a table (call it NewTable) with 4 columns, like so:

NewTableID INT PRIMARY KEY NOT NULL
DataField1 VARCHAR(30) NOT NULL
DataField2 INT NOT NULL
CreatedOn DATETIME NOT NULL

Normally I would set the IDENTITY property on the NewTableID column and run a SQL statement like so:

INSERT INTO NewTable (DataField1, DataField2, CreatedOn)
SELECT DISTINCT DataField1, DataField2, GETDATE()
FROM OriginalTable;

This populates the ID column with ascending integers. I'm thinking that is not OK, as I will be adding records through LightSpeed as the data grows and that will be using the Key Table for ID values. Obviously I would remove the IDENTITY property from that column prior to deploying it, but that still seems like I should not be doing it that way.

What is your recommended method for doing this? Can you assist?

Thanks, Dave

If I understand your concern correctly, you're worried about what values to insert into NewTableID during initial setup so that they don't clash with values later inserted by LightSpeed from the KeyTable. Is that correct?

If so, the answer is that you can use any values that are lower than the current value of the KeyTable. In fact, you can safely use IDENTITY to populate it as long as you check afterwards that you have not overrun the KeyTable. (And if you have, well, just manually poke a higher value into KeyTable.) Alternatively, you can just copy the IDs from OriginalTable:

INSERT INTO NewTable (NewTableId, DataField1, DataField2, CreatedOn)
SELECT DISTINCT OriginalTableId, DataField1, DataField2, GETDATE()
FROM OriginalTable;

This saves messing around turning IDENTITY on and off, and is guaranteed not to overrun KeyTable if OriginalTable's IDs were allocated from the KeyTable. (And LightSpeed is fine with two tables having overlapping IDs -- IDs only have to be unique within a table.) However it may mess up your desired DISTINCT behaviour depending on whether there are duplicate tuples in OriginalTable that you want to eliminate.

Thanks, Ivan. That is my concern, you deciphered it correctly, and you answered my question.

I can't use the IDs from the original table for the very reason you state. This is truly a step towards further normalizing the database, as these values may be duplicated within the original table.

Great stuff you guys peddle... Dave