Import from excel question

I am using an excel spreadsheet to import some data into a table in our sql database.  The issue is that the table contains a field that is a unique number that is generated for each contact address.  It is not used anywhere else in the database, but it is not nullable.  

I am using the import wizard to import this data - Is there a command I can add to the query it generates that will increment this number for every new entry the SQL import tries to insert?
LVL 1
mjburgardAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Because it is an identity column, you can't (normally) specify a value for that.

So if the destination table looks like this
Create table dbo.MyTable(
    MyTableID int identity( 1, 1 )
    , SomeKey int
    , SomeOtherColumns varchar( 200 )
)

Then your insert needs to look something like this
insert dbo.MyTable( SomeKey, SomeOtherColumns )
select
    w.SomeKey
    , w.SomeColumns
from dbo.WorkTable w
left outer join dbo.MyTable m
    on m.SomeKey = w.SomeKey
where
    m.SomeKey is null
;

HTH
  David
0
 
David ToddSenior DBACommented:
Hi,

Is this number in the spreadsheet?

Is this number an identity column in SQL?

As a work around, import the spreadsheet into another table, and then copy the contents from this work table to the main one.

ie
insert dbo.MainTable( col2, col3, col4 )
select w.col2, w.col3, w.col4
from dbo.WorkTable w
left outer join dbo.MainTable m
    on m.SomeKeyCol = w.SomeKeyCol
where
    m.SomeKeyCol is null
;

HTH
  David
0
 
mjburgardAuthor Commented:
I will try the work around -
It is a number, however it is not a key, but it does appear in the Indexes folder of the table.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
David ToddSenior DBACommented:
Hi,

I didn't ask if this was a key.

I asked if it was an identity column.

Regards
  David
Capture.PNG
0
 
mjburgardAuthor Commented:
Yes -
it is an identity column
0
 
mjburgardAuthor Commented:
This was what I needed to get the job finished.  There was a bit more that I needed to do, but this solved the issue I was having.
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.

All Courses

From novice to tech pro — start learning today.