| Author |
Topic |
|
mkswanson
Starting Member
21 Posts |
 Posted - 2009-02-06 : 10:29:52
|
| Hello,I have a problem with a database where the primary key of a table is generating numbers that need to be unique between two tables, and they aren't always.Let me explain a little further:The records are inserted into a table (LineItem), and the LineItemID is always unique (it is the table's primary key). The information is then moved to LineItemHistory, and the LineItemID continues to be this table's primary key. The issue is that some of the LineItem.LineItemIDs being assigned to new data also exists in LineItemHistory. How can I change the values being assigned to LineItem.LineItemID? I would think that if I can simply advance the next value to be greater than any of the values in LineItemHistory, I shouldn't have any more problems...I just don't know how to do it.Thanks for any advice. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 10:40:14
|
| But why would you want to change LineItemID when inserting to history table?? |
 |
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-02-06 : 10:47:37
|
| I want to keep the value from being duplicated, so I want to increase the value of all future LineItem.LineItemIDs so that they won't overlap any existing data.Since this value is asigned by SQL, I need to know how to change the future values it assigns. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 10:52:28
|
quote:
Originally posted by mkswanson
I want to keep the value from being duplicated, so I want to increase the value of all future LineItem.LineItemIDs so that they won't overlap any existing data.Since this value is asigned by SQL, I need to know how to change the future values it assigns.
Define lineitemID as an identity column in your history table. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 10:58:01
|
| Refer this on how to make those changes,http://www.sqlteam.com/article/understanding-identity-columns |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-02-06 : 11:03:14
|
| Thanks for the thoughts, but I do not want to make any changes to the history table.The ID column in the LineItem table is a system generated value. A software application sends the insert command to the SQL server, and the value is generated by SQL. It does not represent any real value. I need to change the value that will be assigned by SQL to this column during future insertions. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 11:18:34
|
| You can then use a table value function to insert.Function will return first table's data along with unique ids which are greater than the max id in your history table. |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-02-06 : 11:33:00
|
| As a side note, I started having problems after I truncated the LineItem table because I was having problems deleting all of the entries after they were copied to the history table (the delete would fail after the TLog balooned to 300 GB, so I truncated the table). SQL seems to have forgotten about any of the previously assigned values, so I need to be able to specify what the next generated value should be. |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-02-06 : 11:35:02
|
quote:
Originally posted by sakets_2000
You can then use a table value function to insert.Function will return first table's data along with unique ids which are greater than the max id in your history table.
I can't change any of the insert commands...they are hard coded into a third party application. I can only change the DB settings. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 11:45:52
|
quote:
Originally posted by mkswanson
quote:
Originally posted by sakets_2000
You can then use a table value function to insert.Function will return first table's data along with unique ids which are greater than the max id in your history table.
I can't change any of the insert commands...they are hard coded into a third party application. I can only change the DB settings.
I don't think theres a way to do it by just changing DB settings. |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-02-06 : 12:45:05
|
| You can set the new value by reseeding the table:DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-06 : 13:04:33
|
| But didn't you say you cant change the structure of the table, neither change the insert statement ??? The above will work only if you change the source table to have an identity column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 08:07:21
|
| Usually for history tables, we never set the primary key of main table as primary key. this is because history table is for capturing the changes in records of main table, so each change happening to a record will be captured and put into history which obviously duplicates the pk of main table. We use a new identity column as pk for history table. it also helps us to identify a single history record easily.An alternate way will be to introduce a audit column like CapturedOn and then make a composite pk out of (LineItemID, CapturedOn). CapturedOn will be default to getdate() and so it will have date of entry into history table as value. |
|
|
|
|
Primary Key Value