Import data from a table without using identity

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Import data from a table without using identity

Author

Topic

rajk
Starting Member

20 Posts

Posted - 2003-05-12 : 03:01:50

Hi,

I am trying to import data from a table (on production testing) into a table on a different server (development testing) by the 'bcp' command. The structure of the table on both the servers is exactly same.

There is an identiy column on the table and I want to avoid using the identity values on the desitnation table (which is blank - as I do not want this column to start from 1..) and get me the same values as it is in production.

I tried using the 'Yes - not for replication' as the identity property value on the destination table and also set the identity_insert to ON before performing the bcp command.

Both of them dont seem to be working.

Thanks in advance
Raj

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-12 : 03:06:38

Hello Raj.

Have you tried the -E parameter (keepidentity)??

rajk
Starting Member

20 Posts

Posted - 2003-05-13 : 07:26:19

No. Can you give me more information on that, please?

Thanks
Raj

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-13 : 08:38:56

Search for "BCP utility" in books online.

"-E

Specifies that the values for an identity column are present in the file being imported. If -E is not given, the identity values for this column in the data file being imported are ignored, and SQL Server 2000 automatically assigns unique values based on the seed and increment values specified during table creation. If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL Server 2000 automatically assigns unique values for the column. For more information, see DBCC CHECKIDENT."

rajk
Starting Member

20 Posts

Posted - 2003-05-15 : 06:08:30

Thank you very much for your time and effort in helping.

Raj

jjeune
Starting Member

1 Post

Posted - 2003-05-15 : 07:04:55

quote:

Hi Raj

Why dont you use DTS for this, by providing the SQL statement for data transfer. Let me know if you may want any more info on this. Thus you would end up selecting only the columns in question that you may require and specify the required destination column, by which the identity column is not targeted
Lemme know if this helps

Have quite a bit of work on these areas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources