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


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?
Who is Participating?
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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.