Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Import errors on primary key

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

604 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