-1

In my csv files that I want to bulk insert into SQL Server, there's text (serial number) that's not in the .csv column format that I would LOVE to use as a primary key.

EX. Data from Engine SQL03423, version 21.04, time, speed, temp june 3 1:00, 90, 200 june 3 1:01, 69, 392

The SQL03423 I want to use as a primary key in my database.

However I get reports from this particular engine daily and if I get to use it as a primary key I'm sure I'll run into the issue of using the same primary key the next time I insert new data which will give me an error.

How do I get around this? I need the serial number regardless even if it's not the primary key. Also if I can't use it as a primary key, how do I create a "dummy" primary key into the target table that will autoincrement even if that particular column is obviously not in the csv files I'm importing? Is this even possible?

I am aware of stored procedures, views, etc in SQL. I have basic knowledge if that helps.

WillaOMO
  • 1
  • 1
  • use something like the [identity property](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property) – S3S Aug 18 '17 at 21:25
  • 1
    Use a combination of columns as the PK, perhaps (Engine,Time). – David Browne - Microsoft Aug 18 '17 at 21:27
  • Is there a way to extract the serial number from the csv file although its not in the column format? The serial number is basically used as a description in the csv files and is not a part of the dataset. However for its uniqueness, I need it to be a part of the data and as one of it's primary keys. I think the combo PKs will work! – WillaOMO Aug 21 '17 at 13:49

1 Answers1

0

I'd suggest using an "artifical" primary key most of the time, which for your question means that you should go for an additional column instead of using the machine's serial number. Preferably, this should be an UUID (GUID) value instead of a simple INT. Why? Please have a look at this great post from Jeff Atwood explaining it: https://blog.codinghorror.com/primary-keys-ids-versus-guids/

Just add a line like this to your table declaration:

THE_IDCOL_NAME UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY

Find more about NewID() here: https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

One of the great advantages of using UUIDs is that you do not have to worry about number ranges overlapping for ID generation. Example scenario from my experience: customer rents machines worldwide and the software logs usage information. Each site uses an idependent database that was being consolidated to the HQ database. If any of the sites would use overlapping number ranges as primary keys, they would have been into trouble. Using GUIDs solved this.

Arminius
  • 1,029
  • 7
  • 11