Insert Error: values do not match table

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

Insert Error: values do not match table

Author

Topic

robbied81
Starting Member

9 Posts

Posted - 2011-10-28 : 06:49:18

Hi,

I have a little problem with a Stored Procedure I'm trying to run. I have created a table in a database and populating it with data from a few other tables. This works fine. I have verified this part of the Stored Proc and can see the data in the table.

The next part is where it fails. I have an excel document where I want to export all this data too. When I run this part of the stored procedure:

DECLARE @NewFile VarChar(2000)
DECLARE @SQL NVARCHAR(4000)

SET @NewFile = 'X:\FTP\excelExports\Stats\StatsTemplate.xls'
SET @SQL = 'insert into OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database='+@NewFile+''',''SELECT * FROM [Sheet11$]'')
SELECT * FROM DBA_Maint..ServerStats'
EXEC(@SQL)

However, when I run this part of the procedure I get the error message:

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

Does anyone have an idea on what is going wrong?

Thanks in advance for any help?

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 06:58:36

is the excel having same columns as your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robbied81
Starting Member

9 Posts

Posted - 2011-10-28 : 07:11:56

quote:
Originally posted by visakh16

is the excel having same columns as your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi,

Sorry literally just found my issue. It was an error my end. I had a similarly named table and was trying to put contents of that in. Have changed to correct table and it works fine.

However, for some reason it is overwriting my headings and making the background colour of each cell, the colour of the heading.

Any ideas?

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 07:22:26

you mean this?


DECLARE @NewFile VarChar(2000)
DECLARE @SQL NVARCHAR(4000)

SET @NewFile = 'X:\FTP\excelExports\Stats\StatsTemplate.xls'
SET @SQL = 'insert into OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database='+@NewFile+';HDR=YES'',''SELECT * FROM [Sheet11$]'')
SELECT * FROM DBA_Maint..ServerStats'
EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robbied81
Starting Member

9 Posts

Posted - 2011-10-28 : 07:25:30

quote:
Originally posted by visakh16

you mean this?

DECLARE @NewFile VarChar(2000)
DECLARE @SQL NVARCHAR(4000)

SET @NewFile = 'X:\FTP\excelExports\Stats\StatsTemplate.xls'
SET @SQL = 'insert into OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database='+@NewFile+';HDR=YES'',''SELECT * FROM [Sheet11$]'')
SELECT * FROM DBA_Maint..ServerStats'EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, the highlighted part was causing the problem.

But having problem with the formatting now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 07:30:13

whats the formatting problem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robbied81
Starting Member

9 Posts

Posted - 2011-10-28 : 09:27:41

quote:
Originally posted by visakh16

whats the formatting problem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Firstly, I have top row with titles. Cell colour is green. When data is pumped into excel all the

Secondly, all fields which are numbers in table are converted to text. Not sure why.

Thanks

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 11:04:30

quote:
Originally posted by robbied81

quote:
Originally posted by visakh16

whats the formatting problem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Firstly, I have top row with titles. Cell colour is green. When data is pumped into excel all the

Secondly, all fields which are numbers in table are converted to text. Not sure why.

Thanks

Rob

you included HDR=YES in connection string as i showed you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources