• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

SQL Import errors on primary key

Got an excel spreadsheet with 11 columns of data, prob 600 rows. In SQL Server 2008 R2 I created a table matching the 11 column names in the spreadsheet, all nvarchar types. I create one extra field in the SQL table called PrimaryKey, type int. I right clicked it and set it as primary key. Looks good so far. Now I use the import wizard to try and load the data from the spreadsheet into the SQL table and it goes through the first few screens, mapping the fields, etc and it gets to the last part of the import wizard and bitches about the Primary Key field, saying it cannot accept null values or something like that. No matter what I did I kept getting the error. The only way I could get it to load was to create a column in the spreadsheet called PrimaryKey and populate it with an incremental # from 1-600. How do I tell SQL that I want SQL to use this field as primary key AND increment the record # by 1 for each record?
thx experts...Bob
0
bobrossi56
Asked:
bobrossi56
  • 2
  • 2
2 Solutions
 
Neil RussellTechnical Development LeadCommented:
You need to set the new column as an identity column.  When you create the table use something like...

PrimaryKey int IDENTITY(1,1) PRIMARY KEY

Open in new window


This will automatically add a value to your field starting at 1 and incrementing by 1 for each record.
0
 
SimonCommented:
Modify the ID field in SQL as IDENTITY to make it auto-increment. Then, try your import again, just mapping the 11 columns to their destinations without trying to write to the ID column.
0
 
bobrossi56Author Commented:
OK, should have said I was a SQL newbie. I have no idea how to do what either of you suggested. All I have done so far is just type the field names and data types into the table columns. So I have a field now in the table that says PrimaryKey, data type int, and it is set to primary key. What are the steps to get from that place to the place you are suggesting.
thx
0
 
SimonCommented:
See this screenshot of the table design window, with the identity setting highlighted. The Column properties section of the window is quite small by default so you have to scroll down it or enlarge it to see the "Identity Specification" group of properties.

Note: You can't retrospectively add the identity property to existing columns without dropping and re-creating the table.
IdentityCol.png
0
 
bobrossi56Author Commented:
Thanks to SimonAdept  for actually explaining how this is done. Works great now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now