Solved

SQL Import errors on primary key

Posted on 2015-01-31
5
138 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
  • 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
Need some help to cast ntext to nvarchar SQL 2000 7 33
SSIS with VPN COnnection 2 77
Create snapshot on MSSQL 2012 3 18
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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