Column name or number of supplied values does not match table definition

Question

Post reply

Column name or number of supplied values does not match table definition

Chrissy321

SSCoach

Points: 15769
More actions
April 9, 2013 at 4:36 pm

#272803

Greetings,
I have a vendor provided procedure that returns two result sets. I am trying to insert the first result set into a table. I believe this should work since I can disregard the second result set.
--This works
CREATE TABLE #Test
(
a float,
b float,
c float,
d float,
e float,
f int,
g float,
h DECIMAL(22,2),
i DECIMAL(22,2),
j DECIMAL(22,0),
k DECIMAL(22,2),
l float,
m float
)
INSERT INTO #Test
SELECT
3.58118449079261E+98,
9.6404720198456E+97,
8.59568543065516E+98,
2.36898164801007E+61,
1.58071941607988E+67,
103,
1.30623880297128E+97,
171334797.19,
131638.64,
404716,
566951665.25,
5504385.09951457,
4.09327353277521E+98
SELECT * FROM #Test
DROP TABLE #Test
GO
--I then create some procs
CREATE PROCEDURE TestProc1
AS
SELECT
3.58118449079261E+98,
9.6404720198456E+97,
8.59568543065516E+98,
2.36898164801007E+61,
1.58071941607988E+67,
103,
1.30623880297128E+97,
171334797.19,
131638.64,
404716,
566951665.25,
5504385.09951457,
4.09327353277521E+98
GO
CREATE PROCEDURE TestProc2 --two return sets
AS
SELECT
3.58118449079261E+98,
9.6404720198456E+97,
8.59568543065516E+98,
2.36898164801007E+61,
1.58071941607988E+67,
103,
1.30623880297128E+97,
171334797.19,
131638.64,
404716,
566951665.25,
5504385.09951457,
4.09327353277521E+98
SELECT
233,
'2012-12-31 00:00:00',
'ABC',
2625859.36,
1.40291573314828E+77,
2773929.43,
2625859.36,
NULL,
3.89158923999005E+83,
1.67548882141181E+197,
0.00507868627580116
GO
--This works
CREATE TABLE #Test
(
a float,
b float,
c float,
d float,
e float,
f int,
g float,
h DECIMAL(22,2),
i DECIMAL(22,2),
j DECIMAL(22,0),
k DECIMAL(22,2),
l float,
m float
)
INSERT INTO #Test
EXEC TestProc1
SELECT * FROM #Test
DROP TABLE #Test
--but this doesn't and I thought it would
CREATE TABLE #Test
(
a float,
b float,
c float,
d float,
e float,
f int,
g float,
h DECIMAL(22,2),
i DECIMAL(22,2),
j DECIMAL(22,0),
k DECIMAL(22,2),
l float,
m float
)
INSERT INTO #Test
EXEC TestProc2
SELECT * FROM #Test
DROP TABLE #Test
Your comments are welcome.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

First, take a look at your temp table and the code in testProc2

CREATE TABLE #Test

(

a float,

b float,

c float,

d float,

e float,

f int,

g float,

h DECIMAL(22,2),

i DECIMAL(22,2),

j DECIMAL(22,0),

k DECIMAL(22,2),

l float,

m float

)

GO

INSERT INTO #Test

SELECT

3.58118449079261E+98,

9.6404720198456E+97,

8.59568543065516E+98,

2.36898164801007E+61,

1.58071941607988E+67,

103,

1.30623880297128E+97,

171334797.19,

131638.64,

404716,

566951665.25,

5504385.09951457,

4.09327353277521E+98

SELECT

233,

'2012-12-31 00:00:00',

'ABC',

2625859.36,

1.40291573314828E+77,

2773929.43,

2625859.36,

NULL,

3.89158923999005E+83,

1.67548882141181E+197,

0.00507868627580116

This works:

INSERT INTO #Test

SELECT

3.58118449079261E+98,

9.6404720198456E+97,

8.59568543065516E+98,

2.36898164801007E+61,

1.58071941607988E+67,

103,

1.30623880297128E+97,

171334797.19,

131638.64,

404716,

566951665.25,

5504385.09951457,

4.09327353277521E+98

But this does not:

SELECT

233,

'2012-12-31 00:00:00',

'ABC',

2625859.36,

1.40291573314828E+77,

2773929.43,

2625859.36,

NULL,

3.89158923999005E+83,

1.67548882141181E+197,

0.00507868627580116

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

... once you fix that you can do something like this (with or without the UNION statement commented.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='deleteme2')

DROP PROC deleteme2;

GO

CREATE PROCEDURE deleteme2 --two return sets

AS

SELECT

3.58118449079261E+98,

9.6404720198456E+97,

8.59568543065516E+98,

2.36898164801007E+61,

1.58071941607988E+67,

103,

1.30623880297128E+97,

171334797.19,

131638.64,

404716,

566951665.25,

5504385.09951457,

4.09327353277521E+98

--UNION ALL

SELECT

9.08118449079261E+98,

3.9904720198456E+97,

8.58888543065516E+98,

2.34448164801007E+61,

1.58071941607988E+67,

103,

1.30623880297128E+97,

171334797.19,

131638.64,

404716,

566951665.25,

5504385.09951457,

4.09327353277521E+98;

GO

--This works

CREATE TABLE #Test

(

a float,

b float,

c float,

d float,

e float,

f int,

g float,

h DECIMAL(22,2),

i DECIMAL(22,2),

j DECIMAL(22,0),

k DECIMAL(22,2),

l float,

m float

)

INSERT INTO #Test

EXEC deleteme1

SELECT * FROM #Test

DROP TABLE #Test

GO

--but this doesn't and I thought it would

CREATE TABLE #Test

(

a float,

b float,

c float,

d float,

e float,

f int,

g float,

h DECIMAL(22,2),

i DECIMAL(22,2),

j DECIMAL(22,0),

k DECIMAL(22,2),

l float,

m float

)

INSERT INTO #Test

EXEC deleteme2

SELECT * FROM #Test

DROP TABLE #Test

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Chrissy321 SSCoach Points: 15769 More actions · Answer 3

Thanks for your response. My procedure has two result sets. Each result set has a different structure. My temp table is matching the first structure. Shouldn't the second result set be disregarded when I run:

INSERT INTO #Test

EXEC TestProc2

I don't have any control over the contents of the procedure. Actually its encrypted so I can't even see it.

My apologies if I am missing something in your post. I thought you could insert the first result set of a procedure with multiple result sets if the destination table matched the procs first result set.