Importing data from different databases.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Julie (anonymous_at_discussions.microsoft.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 07:22:12 -0700

Hi Star,

If as you say code is going to br duplicated then you can
either create a new primary key, or put in a 'range' which
your identity columns adhere to.

If you change the primary key then for the best results
use a uniqueidentifier field, this will create a unique
value irrespective of machine i.e it will always be
different from machine to machine.

The second thing is to have an identity range for each
machine. You would do this by ensuring that each machine
either has a different set of identities i.e

Machine a 0 - 999999
Machine b 1000000 - 1999999
ect
or that it increments by a certian value

i.e.
Machine a 1 11 21
Machine b 2 12 22

For further details have a look at create table in bol

J

>-----Original Message-----
>Hi
>
>We have several databases running on different sites (ex.
sites A,B and C).
>At some point, we want to import data
>from sites A and B into site C.
>
>Our tables have this format:
>
>CREATE TABLE [TableEx] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_TableEx] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>
>As you can see, the Code is generated automatically. We
also have other
>tables related with this one, using that Code.
>
>The problem that we have is this. If we try to import
records from site A
>and B into C, it's possible that we could have duplicates
in the Code.
>I mean, we can have a record from Site A with Code 100
and another record
>from Site B with Code 100.
>
>My first idea in order to resolve this, was to generate a
new Code every
>time that we import a record. Unfortunately, later on we
may need
>to update records (synchronize) and we're going to need
the original Code.
>How can we resolve this problem?
>I also thought that maybe I could add two new fields:
OriginalCode and
>SiteID for each table, to keep track of the original
source, but I not sure
>about
>this either. Another person suggested to generate my own
Code, where I can
>use the first 3 bits for the site information, and the
rest for the regular
>Code.
>However, I don't like this solution. Insertion would be
slower, and every
>time that we want to find records from a specific site, I
would have to add
>an extra condition similar to this:
>
>Get records from site 1:
>
>Select * FROM Table1
>Where Code>100000 AND Code<109999
>
>Any ideas?
>
>Thanks a lot!!
>
>
>
>
>
>
>
>
>
>.
>

Relevant Pages

  • Importing data from different databases.
    ... We have several databases running on different sites (ex. ... it's possible that we could have duplicates in the Code. ... My first idea in order to resolve this, was to generate a new Code every ... I also thought that maybe I could add two new fields: OriginalCode and ...
    (microsoft.public.sqlserver.programming)
  • Re: Combo Box Design and/or Coding
    ... Primary Key: HotelID, Indexed, Yes (No Duplicates), Data Type: AutoNumber ... Primary Key: LocationID, Indexed, Yes, Data Type: AutoNumber ... nothing is being updated in tblHotelLocations. ...
    (microsoft.public.access.formscoding)
  • Re: Compositkey dilema...
    ... If I know that the second such occurrence will supersede the first one, or if there is some external reason that duplicates are never proper, there should be no problem. ... I agree that you may want to define a "No Duplicates" multi-field index on these two foreign key fields if they are not used as a primary key, but by doing so, you re-introduce the problem discussed in paragraph 1. ... couldn't you dispense entirely with [tblStudentClasses]? ... In, you could either let the 2 fields from plus the new [tblTestsID] field form the Primary Key, or just specify that be an Autonumber or otherwise unique without reference to any other fields. ...
    (microsoft.public.access.gettingstarted)
  • Re: Deleting Duplicates in the same table.
    ... If you can't pick one record of the duplicates out from another, then I know of no good method to delete all but one of the duplicated records other than the methods outlined below. ... Build a query based on the table that will identify the primary key values you want to keep and save that as qKeepThese. ...
    (microsoft.public.access.gettingstarted)
  • Re: Deleting Duplicate Records based on "Text" column
    ... CONSTRAINT PRIMARY KEY NONCLUSTERED ... This query in QA produces 411 rows that are duplicates, ...
    (microsoft.public.sqlserver.programming)