Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

import data with identity columns... Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 9, 2013 2:23 PM
Points: 1, Visits: 1
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?
Post #1428933
Posted Sunday, March 10, 2013 4:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 30, 2014 8:31 AM
Points: 647, Visits: 760
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
Post #1428969
Posted Sunday, March 10, 2013 5:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 14, Visits: 1,578
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
Post #1428975
Posted Monday, March 11, 2013 5:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:13 AM
Points: 2,631, Visits: 4,723
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.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1429187
Posted Monday, March 11, 2013 10:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
I think what he's looking for is related to

SET IDENTITY_INSERT DBO.YOURTABLE ON
Post #1429402
Posted Monday, March 11, 2013 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 14, Visits: 1,578
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
Post #1429418
Posted Monday, March 11, 2013 11:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1429419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse