Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 need idea..importing data

Author  Topic 

crazycat503
Starting Member

16 Posts

Posted - 2012-12-26 : 04:38:34
Hi,

i ve a db that is used in a none network environment. All divisions (25 of them) are located in different places that is not connected thru network at the moment. But they are using the same application [.net and sql server 2008] to save their data. Now, i need to bring their data to one centrally used server but i donno how to just deal with primary keys each db generated for its tables.

I am really stuck in this area. How do you think i should proceed? ANy idea would be really helpful.

All guys!

v_yaduvanshi
Starting Member

11 Posts

Posted - 2012-12-26 : 05:04:11
Hi,

Your question needs some more explanation.. if u want to use ur DB from centralised location and schema are same just take full schema details and create a new db with all schema , then change all application's connection string.


quote:
Originally posted by crazycat503

Hi,

i ve a db that is used in a none network environment. All divisions (25 of them) are located in different places that is not connected thru network at the moment. But they are using the same application [.net and sql server 2008] to save their data. Now, i need to bring their data to one centrally used server but i donno how to just deal with primary keys each db generated for its tables.

I am really stuck in this area. How do you think i should proceed? ANy idea would be really helpful.

All guys!



Virendra Yaduvanshi
MCTS, MCITP SQL Server
http://wikidba.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 05:08:41
sounds like what you're after is kind of enterprise data warehouse.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

crazycat503
Starting Member

16 Posts

Posted - 2012-12-27 : 04:09:34
I am not sue what you guys mean but here is a more detailed explanation...

My company has different divisions but they all do the same task. THE divisions are physically located in different places (some close, some very far from eachother). Since they do the same task, they use the same application and database which is installed in each division's computers (or server). NOw what is needed is to have them send their data to the main office and all data be stored in the one server and one database which is exactly the same as the db they use in thier offices. The tables use identity key and am worried about that row in particular. I recently heard making it GUID would have helped altho based on the readings i did, i can' see how the situation would be different. Note the divisions are not connected thru any means of network so the data they will bring will be either exported from SQL SERVER thru Generate Scripts or thru a file generated by a locally developed application which does more or less the same thing with few exceptions.

how do i treat the unique ids that could be well used/repeated between all the divisions? hope i am clear now.

All guys!
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-27 : 12:51:32
You may want to add a few columns to the warehouse tables so that you can trace origin and create a new identity column. Change the current identity column to be non-identity and non-unique. Then your unique key would be origin plus old-identinty.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-01-02 : 15:13:04
You could add a column with Seed:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

The idea is that division1 will have 0 to 1000, division1 1001 to 2000, and so on so forth.

Then when bringing them into a central location, they will co-exsist peacefully.

If you need to repeat the process, you have to build in rooms for growth in this ID column.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-03 : 01:24:38
why not add a centralised set of tables with surrogate key which would be an identity key where you populate data from individual tables in various geographic location. then apply replication to publish these tables to each of individual servers and use them in reports where you want all data to be integrated.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

crazycat503
Starting Member

16 Posts

Posted - 2013-01-08 : 03:43:42
quote:
Originally posted by Hommer

You could add a column with Seed:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

The idea is that division1 will have 0 to 1000, division1 1001 to 2000, and so on so forth.

Then when bringing them into a central location, they will co-exsist peacefully.

If you need to repeat the process, you have to build in rooms for growth in this ID column.



Hey Hommer,

Can you tell me more about that? I don't quite follow you. Each division have unique id (such as 123)...any way i can use that number in the unique column id? How does the range you specified [0-1000] work and how would you think of it for the future?

All guys!
Go to Top of Page
   

- Advertisement -