General discussion

Locked

temporaty tables

By saleha ·
how do i insert data into temporaty table from a neither table in a stored procedure?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

temporaty tables

by Tim Parkins In reply to temporaty tables

Hi,

In your stored procedure, simply use the insert statement:

INSERT INTO temp_table (
SELECT *
FROM other_table);

COMMIT;

Hope this helps!

Tim

Collapse -

temporaty tables

by saleha In reply to temporaty tables

The question was auto-closed by TechRepublic

Collapse -

temporaty tables

by Articulated Mandible In reply to temporaty tables

You don't mention what application you are trying to do this in, but based on your use of the phrase "stored procedure" I'm guessing it might be SQL Server. If so, there are several ways to do it,here is one of the simpler ones:

CREATE PROCEDURE SampleSP AS
SELECT (Field1,Field2)
INTO #TempTable
FROM(
SELECT Field1,Field2
FROM MainTable
WHERE Criteria
)

Remember that you must prepend the temporary table name with the # sign to create a true temporary table (onethat will be dropped automatically when the SP ends), otherwise you will have created a new table that must be explicitly dropped within the procedure.

Also remember that the "Select_Into/Bulkcopy"
option must me turned on for your server for the Select Into statement to work

Collapse -

temporaty tables

by saleha In reply to temporaty tables

The question was auto-closed by TechRepublic

Collapse -

temporaty tables

by chrandrob In reply to temporaty tables

I wouldn't suggest using the Select . . . Into statement to create your temp table. If one of the data columns is empty or null the process will fail when it tries to create the temp table. It also allows you to add defaults, etc., to your table. SQL Server will not create a column with a data size of zero.

It's better to plan ahead, explicitly create the temp table, insert the data, and I always manually drop the temp table as a matter of good programming habit.

Example:

Create Table #tmpTableOne (
ID int identity (1, 1) not null primary key,
Field1 int not null default (0),
Field2 varchar (50)
)

Insert Into #tmpTableOne (Field1, Field2)
Select Field1, Field2
From TableTwo

. . . more code . . .

Drop Table #tmpTableOne

Collapse -

temporaty tables

by saleha In reply to temporaty tables

The question was auto-closed by TechRepublic

Collapse -

temporaty tables

by saleha In reply to temporaty tables

This question was auto closed due to inactivity

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums