Integer identity value handling in export / import

Hi,

I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?

CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||

I am Hitesh.

I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.

Thanks.

Integer identity value handling in export / import

Hi,

I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?

CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||

I am Hitesh.

I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.

Thanks.

Integer field question

Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
Neil
I don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.