SQL Server Stored Proc results to Table

Posted on 2011-10-24
Last Modified: 2012-05-12
Hello.  I have SQL stored proc that returns 5 columns.  I want to execute that SP and place the data into a temp table, but I only want to return 3 of the 5 columns.  Logically I assumed I could do this by defining my temp table with the 3 fields I need, then run an INSERT INTO also specifying the 3 fields.  

Problem is when I run it I receive an error message "Column name or number of supplied values does not match table definition".

Sample code is below.  Any way I can execute the SP and only return a, b, and c into the temp table?

Create Table #TempTable ([a] varchar (20) null,
			 [b] varchar (20) null,
			 [c] varchar (20) null)
Insert Into #TempTable  ([a], [b], [c])

Execute mystoredproc -- this sp returns fields a,b,c,d,e

Open in new window

Question by:jobprojn
    LVL 59

    Expert Comment

    by:Kevin Cross
    If I am not mistaken, you will need to insert all the columns and then just drop the ones you don't want later OR just have two tables. One to get initial results and the other to store the three columns you want if going to hold other data. If the table is just holding the stored procedure data, it is probably makes sense to have all the columns and just select a, b, c only or as I said alter table and drop the extraneous columns.

    Author Comment

    mwvisa1, thanks for your comments.  The challenge is that the number of fields in the SP (mystoredproc) is continually changing.  We have numerous other queries/sp's that rely on [mystoredproc] and we would like a way to be able to add fields to [mystoredproc] without having to also update all the many other queries/sp's that use it.  Hope this makes sense.
    LVL 59

    Accepted Solution

    One option if these are singular values is to add OUTPUT parameters. You can have @a, @b, and @c. Another option is to have a single XML parameter that represents the output you want in XML format. You can take that and parse to your table. I know table-valued parameters were added to SQL 2008, but could not remember if OUTPUT was allowed--it is not, but found an interesting option in this thread:

    Suggestion is to create a self referencing linked server, so that you can use OPENQUERY which gives you the ability to then select what columns you want. If your stored procedures all use a common nomenclature for the first three columns this may be a good workaround.

    Author Closing Comment

    Thank you.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now