How to insert stored procedure output into 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert stored procedure output into table

Author

Topic

mpandey
Starting Member

5 Posts

Posted - 2009-01-23 : 03:36:21

Hi there,

I tried to insert the stored procedure output into a table but got an error message.
I created a table:

Create table _constraintsData
(
objectName varchar(500),
constraintType varchar(200),
constraintName varchar(200),
deleteAction varchar(200),
updateAction varchar(200),
statusEnabled varchar(200),
statusForReplication varchar(500),
constraintKeys varchar(2000)
)

After then, I tried to insert the data into this table as follows:

insert into _constraintsData
exec sp_helpconstraint 'class'

where class is a temporary table I created for a practice purpose.

I got the following error message:

Msg 213, Level 16, State 7, Procedure sp_helpconstraint, Line 236
Insert Error: Column name or number of supplied values does not match table definition.

I spent my whole night but could not figure it out. If anyone has any idea it would be very helpful to me and I truely appreciate it.
Thank u.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-23 : 03:39:31

insert into _constraintsData (field1,field2...........)
exec spname parameters

for ex
exec usp_lengthfinder 'string'

Jai Krishna

mpandey
Starting Member

5 Posts

Posted - 2009-01-23 : 03:46:46

Hi Jai Krishna,

I tried the way you told, but still gave me the same error.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-23 : 03:53:12

quote:
Originally posted by mpandey

Hi Jai Krishna,

I tried the way you told, but still gave me the same error.

The fields in the insert statement must match the no of o/p parameters ur stored procedure returns

Jai Krishna

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 03:53:59

quote:
Originally posted by Jai Krishna

quote:
Originally posted by mpandey

Hi Jai Krishna,

I tried the way you told, but still gave me the same error.

The fields in the insert statement must match the no of o/p parameters

Jai Krishna

also datatypes....

mpandey
Starting Member

5 Posts

Posted - 2009-01-23 : 04:05:24

Hi there,

sp_helpconstraint is a system defined stored procedure in a SQL Server. And when I executed it, it has 7 output columns. And my table contains 7 columns to insert the data. So, I didn't understand where is the error?
And I don't know how to find out the datatype of stored procedures parameters?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-23 : 04:08:13

insert into _constraintsData (specify the 7 fields here)
exec sp_helpconstraint 'class'
Jai Krishna

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 04:10:03

quote:
Originally posted by mpandey

Hi there,

sp_helpconstraint is a system defined stored procedure in a SQL Server. And when I executed it, it has 7 output columns. And my table contains 7 columns to insert the data. So, I didn't understand where is the error?
And I don't know how to find out the datatype of stored procedures parameters?

execute the below statement

sp_helptext 'sp_helpconstraint'

There u get the sp content where u can see parameters datatypes as well as output columns datatypes..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 04:20:25

quote:
Originally posted by mpandey

Hi there,

sp_helpconstraint is a system defined stored procedure in a SQL Server. And when I executed it, it has 7 output columns. And my table contains 7 columns to insert the data. So, I didn't understand where is the error?
And I don't know how to find out the datatype of stored procedures parameters?

insert into Newtable ( field1 nvarchar(146), field2 sysname, field3 nvarchar(64), field4 nvarchar(64),field5 nvarchar(64),field6 nvarchar(64),field7 nvarchar(2126))
exec sp_helpconstraint 'yourtablename' ,'nomsg'

some thing like above where field1 ,field2....field7 are columns in table which you are going to insert the result of sp....the datatypes
which are mentioned in above stmt should match with newtable column datatypes..

Sp_HelpConstraint is a system defined sp which is used to get constraint information of a table which we passed as parameter to that sp...

Actually we should not mention the datatypes of columns in insert statement but i just mentioned in insert statement to make you aware of datatypes of columns in new table

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 04:28:09

try this one

create table #temp
( field1 nvarchar(146), field2 sysname, field3 nvarchar(64), field4 nvarchar(64),field5 nvarchar(64),field6 nvarchar(64),field7 nvarchar(2126))

insert into #temp ( field1 , field2 , field3 , field4 ,field5 ,field6 ,field7 )

exec sp_helpconstraint 'existingtable' ,'nomsg'

select * from #temp

mpandey
Starting Member

5 Posts

Posted - 2009-01-23 : 11:08:09

Thank You Reky, it works.
I really appreciate your help.
Thank you jai krishna too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:51:56

also see this:-

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-24 : 00:46:32