Question about IDENTITY columns.

We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
This table is present in 2 databases Db1 and Db2. We want to keep them
both in sync daily. The mapping of Name -> ID should be the same in
both the tables. We allow only Db1 tables to be updated. So we would
like to have a program that daily truncates [Db2].[TAB1] and creates
and executes a DTS package to transfer the table from DB1 to DB2.
My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
will get copied to DB2. If we delete the row with ID 3, will the next
syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
the table as 1,2,4,5?
Thanks
YashIf you create the field in table 2 as an identity field you will have
1,2,3,4 rather than 1,2,4,5 when trucating and re-populating. You could use
a table trigger to keep both tables in sync.
groutme in SO Cal
groutme_alternate@.sbcglobal.net
<yashgt@.yahoo.com> wrote in message
news:1109006625.544445.159200@.c13g2000cwb.googlegroups.com...
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
It will copy 1, 2, 4,5. But you have to set the property "Enable Identity
insert" in the options tab of the "Transform Data Task" properties, in order
to allow explicit values to be inserted into the identity column.
AMB
"yashgt@.yahoo.com" wrote:

> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||>> If we delete the row with ID 3, will the next syncing execution cause the
There is an option in DTS which allow identity inserts under the
transformation section. When this is off the values are inserted without
generting new ones. Otherwise the new values will be generated in the
sequence based on the seed and increment set on the identity column for the
table.
Anith|||If the data in DB2 gets replaced every day then what's the point of giving i
t
an IDENTITY column? Make it a regular numeric column so that you have full
control over what values go in there.
David Portas
SQL Server MVP
--