Solved

SQL Import errors on primary key

Posted on 2015-01-31
5
144 Views
Last Modified: 2015-02-02
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
Comment
Question by:bobrossi56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 100 total points
ID: 40581580
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
 
LVL 18

Accepted Solution

by:
Simon earned 400 total points
ID: 40581586
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
 

Author Comment

by:bobrossi56
ID: 40581601
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
 
LVL 18

Expert Comment

by:Simon
ID: 40581656
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
 

Author Closing Comment

by:bobrossi56
ID: 40583827
Thanks to SimonAdept  for actually explaining how this is done. Works great now.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question