Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

insert into table execution of a stored procedure Expand / Collapse
Author
Message
Posted Saturday, November 30, 2013 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
Hello
I have this table
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))

I want to fill it with the execution of this stored procedure

insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''

the execution of stored procedure is bellow

id Mid ValueMid CatParent Enabled LastUpdate company
1 20 100001 NULL 25 NULL NULL
2 20 100007 NULL 25 NULL NULL
3 20 100030 NULL 25 NULL NULL
4 20 100042 NULL 25 NULL NULL
5 20 100043 NULL 25 NULL NULL

BUT I see this error

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

I don't know what is wrong.
Post #1518624
Posted Saturday, November 30, 2013 12:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 12:46 AM
Points: 2, Visits: 1
SQL Server Version?
Post #1518626
Posted Saturday, November 30, 2013 12:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
sql server 2008 R2
Post #1518627
Posted Saturday, November 30, 2013 12:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
Is your server case sensitive by any chance? Also, what is the compatibility level for the database set to?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518629
Posted Saturday, November 30, 2013 1:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 12:46 AM
Points: 2, Visits: 1
Are the datatypes of the output columns from stored procedure same as the Table Variable?
Post #1518630
Posted Saturday, November 30, 2013 1:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 12:52 AM
Points: 36, Visits: 101
Yes they are same
Post #1518634
Posted Saturday, November 30, 2013 7:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
can you please provide definition of your sql procedure


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1518647
Posted Monday, December 2, 2013 1:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
I ran following on sql server 2008 r2 with no errors . you prbly need to check your sproc definition.

if exists(select * from sys.objects (nolock) where name = 'UserMidList' and type = 'P')
begin
exec('drop proc UserMidList');

end
go
create proc UserMidList as

select 1, 20, 100001, NULL, 25, NULL, NULL
union
select 2, 20, 100007, NULL, 25, NULL, NULL
union
select 3, 20, 100030, NULL, 25, NULL, NULL
union
select 4, 20, 100042, NULL, 25, NULL, NULL
union
select 5, 20, 100043, NULL, 25, NULL, NULL



go

declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))

insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)
exec UserMidList

select * from @UserMidListTable
go

select @@version
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Post #1519012
Posted Tuesday, December 3, 2013 12:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:11 AM
Points: 17, Visits: 552
You could use OPENROWSET to create a temporary table (example and link to example web page below) and check that the stored procedure definition matches your table variable definition.


SELECT * into #Temp2
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')


From http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx
Post #1519381
Posted Wednesday, December 4, 2013 4:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
The problem with using OPENROWSET is that it requires "SA" privs. That's ok for jobs but should not be ok for application logins.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1519831
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse