I am totally new to SQL Server so apologies if this seems a silly question. I am trying to schedule a daily import from a csv file into an application that runs on SQL server. I can create a DTS package to do this but the main problem is the primary key of the table is a surrogate key that automatically increments. I cannot import a csv file without supplying a memberID(primary key). I can supply a memberID in the base csv file if I know the next number but this is not always possible as multiple sources can insert a new row into the table. If I use SQL to insert a new row into the SQL Server db (by using SQL Query Analyzer) the database will automatically increment a memberID and the value does not need to be supplied. DTS seems to be the easiest way to schedule a daily import but I cannot seem to get around the auto incrementing primary key issue. All feedback is welcome & sorry for being the annoying novice!

Claire,

Do not map the key column and let it auto-increment.

Ilya

"Claire" <Claire (AT) discussions (DOT) microsoft.com> wrote

application that runs on SQL server. I can create a DTS package to do this
but the main problem is the primary key of the table is a surrogate key that
automatically increments. I cannot import a csv file without supplying a
memberID(primary key). I can supply a memberID in the base csv file if I
know the next number but this is not always possible as multiple sources can
insert a new row into the table. If I use SQL to insert a new row into the
SQL Server db (by using SQL Query Analyzer) the database will automatically
increment a memberID and the value does not need to be supplied. DTS seems
to be the easiest way to schedule a daily import but I cannot seem to get
around the auto incrementing primary key issue. All feedback is welcome &
sorry for being the annoying novice!

Have a look at this article: It will help you get around some of the issues
you may encounter.

Problems With IDENTITY() and the DataPump task.
(http://www.sqldts.com/default.aspx?293)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Claire" <Claire (AT) discussions (DOT) microsoft.com> wrote

application that runs on SQL server. I can create a DTS package to do this
but the main problem is the primary key of the table is a surrogate key that
automatically increments. I cannot import a csv file without supplying a
memberID(primary key). I can supply a memberID in the base csv file if I
know the next number but this is not always possible as multiple sources can
insert a new row into the table. If I use SQL to insert a new row into the
SQL Server db (by using SQL Query Analyzer) the database will automatically
increment a memberID and the value does not need to be supplied. DTS seems
to be the easiest way to schedule a daily import but I cannot seem to get
around the auto incrementing primary key issue. All feedback is welcome &
sorry for being the annoying novice!

Thanks Allan,

Only noticing while replying that you were the author of the excellent document you referred me to. This explained exactly the problem I was experiencing. I followed your document & can now get SQL Server to auto increment my identity/primary key column. Thanks very mcuh for your help.

Regards,

Claire

"Allan Mitchell" wrote: