Importing Excel into SQL - error

[smilbert]

I'm using MS Excel 2003 and MS SQL 2000.

I needed to do a LOT of data entry, so I exported a SQL table from my database into Excel. I deleted the existing data and created the additional data I need.

When I import the data (and select Append), I get an error that the primary key is duplicated on the LAST ROW (it said row 285 and my spreadsheet had 286 rows--I assume SQL started with 0). If I delete the last two lines, it gives me the same error for line 283.

I think it's trying to import a blank row after my data. Is there any type of END command something I can do the Excel document so it knows to stop after the last row of real data?

 

[gmmastros]

Instead of appending the data in to an existing table, I would suggest that you import the data in to a temporary staging table. You can then check for blank rows or any other data issues. Once the data is 'cleaned up' then insert the data from the staging table to the real one.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smilbert]

Excellent idea, thanks. You say "insert" the data from one table to the other. Is there an easier way to do this besides the Import Data wizard?

 

[jbenson001]

use Select Into or Insert Into

 

[SQLSister]

You can't use selct into with an exisiting table. You would run an insert statement something like

insert table1 (field1, field2, field3)
select field1, case when field2 >10 then field2 else field4 end, 'test' from stagingtable s
left join table1 t on s.idfield = t.idfield
where t.idfield is null

Note it is important to use the actual field names and not select * especially if you have an identity field which is creating an autonumber. That field cannot be included in the select or the insert part of the statment or you will get an error. You can also use case statements to do specific business rule processing or add the text to populate a specific value that isn't in your staging table. The left join part of the sample insert will check to see if the record exists in the table you aredoing the insert into and only insert the records which do not have a match.

Of couse you could just do a plain vanilla insert as well.

"NOTHING is more important in a database than integrity." ESquared