0

I'm trying to achieve this using C#, to insert data from a csv file into Microsoft SQL Server.

I have a CSV file that does NOT have an identity column. I would like to insert data from this CSV into a table on Microsoft SQL Server that has an identity column, however, the identity column was not set to auto_increment.

Is there a way to achieve this? I looked into BULK INSERT in SQL but I couldn't figure out how to get the identity column to work properly.

I was thinking to connect to the database and table to find out what the current largest id number is, then modify the CSV to include the identity column, the BULK INSERT the new CSV. I think the idea might work, but I was just wondering if there is some other ways (more convenient way) to solve the problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sora0419
  • 2,308
  • 9
  • 39
  • 58
  • 1
    is this a one time thing or are you writing a program to do this on a regular basis. If it is one time I would ditch C# and SQL queries and use [the import wizard built in to Sql Server Management Studio](http://technet.microsoft.com/en-us/library/ms140052.aspx). – Scott Chamberlain Nov 05 '13 at 16:12
  • @ScottChamberlain I'm writing a program to do the job on a regular basis. What it is is that I'm trying to insert data from one server/network to another, and because of the privilege issue, I'll have to export the data to CSV first than insert it to another. – sora0419 Nov 05 '13 at 16:15
  • *however, the identity column was not set to auto_increment* - this contradicts itself; **either** it's a column with the `IDENTITY` flag set - and then it **IS** auto-incrementing - or it's **NOT** auto-incrementing, which means, the `IDENTITY` flag is **NOT** set .... – marc_s Nov 05 '13 at 16:49
  • @marc_s sorry for not explaining well, but when I say the identity column in the server, it's technically not the identity column, it is just a column named "id", it was set to NOT NULL, nothing else. – sora0419 Nov 05 '13 at 17:14

1 Answers1

0

In SQL Server, identity and auto increment are the same thing (identity is the SQL Server implementation of auto increment).

(Reference: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ef62d0c8-440f-4b27-8641-18ecd45d3f6c/what-is-the-difference-between-auto-increment-and-identity?forum=transactsql)

Also, for bulk inserts, I would highly recommend using SqlBulkCopy (built into System.Data). This is a good article outlining how to use it: https://github.com/geersch/EntityFrameworkBulkCopy

See here for using SqlBulkCopy with identity inserts: SqlBulkCopy Insert with Identity Column

(Use SqlBulkCopyOptions.KeepIdentity if you want to specify the identity value from code, or just exclude the identity column from the input and use the normal options to have SQL Server automatically assign it based on auto increment.)

Christophe Geers
  • 8,564
  • 3
  • 37
  • 53
mayabelle
  • 9,804
  • 9
  • 36
  • 59
  • sorry for not explaining well, but when I say the identity column in the server, it's technically not the identity column, it is just a column named "id", it was set to NOT NULL, nothing else. – sora0419 Nov 05 '13 at 16:36