March 9, 2013 at 2:02 pm
Really going crazy here!
I have table1 and table2 [for e.g. purpse]. They are of exact design:
id [autoincrement, primary key, int]
name [varchar(10))
creatorid [int] --code of the office which inserted the row?
both can contain the same id value. I need to bring table1 data to table2 while keeping the data. The db was designed for use in a NT but now there are offices which are not networked and their data needs to come to the central office thru export. How do we do that?
Every row got id of the office which created it. The problem is that the id column is referenced in other tables. Any insight please?
March 10, 2013 at 4:26 am
I don't think i really understand your question here.
But for what i think you mean, the best way to go would be to change the tablestructure of table 1 with includeing a new column that could hold the original id from the old table2
Could that help you out?
Wkr,
Van Heghe Eddy
March 10, 2013 at 5:02 am
Seed the autoincrement differently for each office?
e.g.
1 for head office
100,000,001 for 1st branch
200,000,001 for 2nd branch
....
2,000,000,001 for 20th branch
if you need more rows/branches than can be accomodated with int (including -ve int seeds), try bigint.
//or//
Use creatorid & id as composite primary key (and composite foreign key in related tables).
Chris
March 11, 2013 at 5:35 am
rut_new (3/9/2013)
Really going crazy here!I have table1 and table2 [for e.g. purpse]. They are of exact design:
id [autoincrement, primary key, int]
name [varchar(10))
creatorid [int] --code of the office which inserted the row?
both can contain the same id value. I need to bring table1 data to table2 while keeping the data. The db was designed for use in a NT but now there are offices which are not networked and their data needs to come to the central office thru export. How do we do that?
Every row got id of the office which created it. The problem is that the id column is referenced in other tables. Any insight please?
Can you give us some DDL and sample data depicting your problem?
It will then be easier for us to give you a tested solution.
If you are not sure on how to do this, please check the link in my signature.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 11, 2013 at 10:54 am
I think what he's looking for is related to
SET IDENTITY_INSERT DBO.YOURTABLE ON
March 11, 2013 at 11:53 am
IDENTITY_INSERT won't fix the issue of duplicate id coming from different offices.
If IDENTITY_INSERT allowed you to introduce a duplicate id despite the PK constraint, what happens to related rows in other tables? ...chaos!
Chris
March 11, 2013 at 11:55 am
The big challenge you have here is that you have two tables each with an identity primary key and foreign keys. You are going to have to do quite a bit of work to pull this off. Probably the approach I would take is to add a new column in the table you want to combine from. This new table needs to have a new int column that will become the key when the data moves. Update that column to have some values that don't exist in the current table. Then you will need to find and update all the associated rows in the other tables so the linkage will remain intact (but using the new column). Then insert your data to the other table using the newly created column as your value for the primary key in the new destination.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply