Transferring Data results in loss of Primary Key etc

Transferring Data results in loss of Primary Key etc

Post by Mark S » Tue, 14 Dec 1999 04:00:00



Hi all,

    When using the Data Transformation Services to transfer the data in one
database to a new database, the incrementer and primary key identities are
lost!  If you do a direct copy of the tables between the databases, the ID
and primary key relationships do not carry over.  Also if you use the SQL
transfer option, it crashes before transferring any data.  The only way I
have found that is successful is to backup and restore the database.  I was
hoping for a better solution.  PLEASE HELP!

    Mark

 
 
 

Transferring Data results in loss of Primary Key etc

Post by Brian Mora » Tue, 14 Dec 1999 04:00:00


I'm not sure what happens when a transfer "crashes", or what eactly you mean
b a direct copy... but DTS will definitely not move keys...

To do what you want...

script the tables using the Gen SQL Scripts option.... then use DTS, insert
into, BCP, etc... or what ever you want to move data...

--
Brian Moran

CIBER Washington D.C.
MCSE, MCSD, SQL Server MVP
SQL Server Magazine Columnist
President, Capital Area
SQL Server Users Group


Quote:> Hi all,

>     When using the Data Transformation Services to transfer the data in
one
> database to a new database, the incrementer and primary key identities are
> lost!  If you do a direct copy of the tables between the databases, the ID
> and primary key relationships do not carry over.  Also if you use the SQL
> transfer option, it crashes before transferring any data.  The only way I
> have found that is successful is to backup and restore the database.  I
was
> hoping for a better solution.  PLEASE HELP!

>     Mark


 
 
 

1. Primary key, Foreign key, referential integrity, etc?

Hi, I need to make a database for used in my VB program. I have some
doubts about the VB Database:
1. I know how to set the Primary key using VISdata, but how to set
foreign key?
2. How to setup a lookup table, and apply it in VB? (I meant those
that have the descriptions of some codes, which these codes are used
in base table).
3. How to setup referential integrity in VB?
Please give me hints. Thanks in advance.

2. Problem with Trusted Connections

3. changing primary keys in order to transfer data

4. using SQL Server Temporary tables with ODBC

5. Loss of primary keys

6. Bellcore's OSCA architecture: design and implementation experience

7. loss of storage parameters for primary key indexes

8. some more questions re: database for book

9. Help finding which columns are indexes, primary keys etc using T-SQL (SysObjects/SysColumns tables)

10. Relational databases and primary keys etc??

11. Relational design and primary keys etc

12. update primary key [aid] to identity(300,1) Primary key

13. Numeric Primary Key vs. Combination Primary Key