Solved

Import from excel question

Posted on 2014-01-30
6
187 Views
Last Modified: 2014-02-11
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?
0
Comment
Question by:mjburgard
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 
LVL 1

Author Comment

by:mjburgard
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

I didn't ask if this was a key.

I asked if it was an identity column.

Regards
  David
Capture.PNG
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:mjburgard
Comment Utility
Yes -
it is an identity column
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:mjburgard
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now