?
Solved

SQL Import errors on primary key

Posted on 2015-01-31
5
Medium Priority
?
147 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 400 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 1600 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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