SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


import data with identity columns...


import data with identity columns...

Author
Message
rut_new
rut_new
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
Van Heghe Eddy
Van Heghe Eddy
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 912
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
Chris_M
Chris_M
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1948
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
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5986 Visits: 5280
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/
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2119 Visits: 1119
I think what he's looking for is related to

SET IDENTITY_INSERT DBO.YOURTABLE ON
Chris_M
Chris_M
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 1948
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62005 Visits: 17954
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search