Appending data from Excel into SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Appending data from Excel into SQL

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2012-03-05 : 10:44:12

I have an SQL table that I need to update from an Excel spreadsheet. The SQL table only needs its first three columns updated, how can I append the Excel data? The Excel sheet is in the same order and has the same column names as the SQL table.

meef
Posting Yak Master

113 Posts

Posted - 2012-03-05 : 11:26:26

I've used the import wizard to get the excel table into SQL and its on temporary table, but now if I try this statement I get an error:

use db
INSERT INTO dbo.fuel SELECT fscco, monday, avgfuel FROM dbo.fuel$

ERROR: "Insert Error: Column name or number of supplied values does not match table definition."

The columns are identical in both tables.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-05 : 17:32:19

Since you are not providing all the columns in dbo.fuel, you have to explicitly list which columns you are supplying:

INSERT INTO dbo.fuel (fssco, monday, avgfuel) SELECT fscco, monday, avgfuel FROM dbo.fuel$

That assumes that the remaining columns in dbo.fuel are either nullable, or have default values.

meef
Posting Yak Master

113 Posts

Posted - 2012-03-06 : 08:41:06

Thanks, that got me farther but I ran into what you mentioned - the rest of the columns don't have default values and some can't be NULL. Not sure of a way around this unless I just put "dummy" data in the Excel table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-06 : 09:07:51

You can put dummy data in the Excel sheet, or even in the query itself:

INSERT INTO dbo.fuel (fssco, monday, avgfuel, intCol1, charCol1) SELECT fscco, monday, avgfuel,0,'dummy' FROM dbo.fuel$

meef
Posting Yak Master

113 Posts

Posted - 2012-03-06 : 09:20:34

I didn't feel like re-importing the Excel sheet so I just did it in SQL and used data that was similar to the rest. Thanks for the help, I got the fields I needed appended.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 09:44:26

you're building a production process?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources