So I've downloaded a sample database. It originally didn't have an ID column so I inserted one in through Excel. Anyways, I went through DatabaseName > Tasks > Import Flat File and the usual process, and arrive at the modify columns below.

Import Flat File Wizard

My issue is, where can I specify that the ID column should be auto-incremented? And if I can't do that here, what should I do as a workaround?

  • I think you need to create the table with the auto-increment ID outside of the import wizard. But I'm not clear on your needs, is the ID coming from your flat file, or do you want SQL to generate it? Jun 11 '20 at 12:05
  • @JonathanFite Either one is fine tbh, I just need an auto-incrementing ID with the data from the flat file (I generally prefer integer ID's over strings). The table in my flat .csv file contains ID for its 500 people, numbered from 1 - 500. I want 501 to be added by default when I add a new row somehow. I'm kind of new to SQL so I wouldn't know if it's possible to create a table with autoincrement ID, then import the data from the CSV file. If it is, I'd likely resort to that. Jun 11 '20 at 13:24

You cannot alter a column to be an IDENTITY column.

What you'll need to do is create a new column which is defined as an IDENTITY.

Then use an update statement to update the new column from the old one (with SET IDENTITY_INSERT ON before and OFF after the update).

Once this is done, drop the old column.

Fianlly, rename the new one to the old name.

  • Okay... Let me test this. If it works, I'll mark this as the answer. Jun 11 '20 at 13:29
  • It works... but now SQL won't let me move that new column to the first column on the table lol. Either way, you did solve the problem. Jun 11 '20 at 13:42
  • 1
    Usually, that should not be a big deal if the column is not "placed" at the start as you should specify column name in the select or update (as a best practice). If you want it as the first, then I guess you'll need to create your table first (with the identity) and then load it from the csv Jun 11 '20 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.