Inserting data into a table from a stored procedure MS SQL 2008

Posted on 2012-09-08
Medium Priority
Last Modified: 2012-09-09

I am using MS SQL 2008.

I have a stored procedure that is called by other stored proedures.

This store procedure generates a temporary table and fills it with data. It does some manipulations on the data and then returns back to the calling stored procedure.

However now i want to create a "real" table in the database and use the stored procedure to fill it.

I seemed to have trouble doing insert ** into MyTable from ... because it claims the table exists - which it does!  I have created an empty table.  Does the insert into statement also create the table?  I wanted to do it myself so i have some control over the column definitions.

What is the best way to do this?
Question by:soozh
1 Comment
LVL 16

Accepted Solution

DcpKing earned 2000 total points
ID: 38379919
If you use this code:

declare @a int, @b int, @c varchar(24), @d datetime
--       Add code here to give values to variables
select  @a, @b, @c, @d into #temptab

then you'll get a temporary table called #temptab with the structure [int, int, varchar(24), datetime], as you might expect. The "into" keyword in the select statement causes sql server to create an appropriate table structure to use for the data.

You don't have to make this a temp table - you can equally well use

select  @a, @b, @c, @d into permtab

Then refresh the tables list in SSMS and you should see your new table. However, do it again and you'll get an error because the table is already there!

A more controlled way would be to have an already-created table and then use the following code in the stored procedure:

--     clear the table first if needed:
truncate table permtab
--     and then put the data in
insert into permtab
    select  @a, @b, @c, @d



Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Loops Section Overview

809 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