INSERTING INTO A TEMPORARY TABLE OUT PUT OF BATCH QUERIES

Question

INSERTING INTO A TEMPORARY TABLE OUT PUT OF BATCH QUERIES

Aswani kumar

Mr or Mrs. 500

Points: 556
More actions
November 1, 2005 at 7:31 am

#89877

Hi GUYS,
REALLY SO MUCH INTERESTING REQUIREMENT FOR ME
IAM GIVING SAMPLE TABLES :
In a table a columns consists the batch sql queries like this
select * INTO TABLE5 from EMP
SELECT * INTO TABLE6 FROM DEPT
SELECT * FROM TABLE5,TABLE6 WHERE EMP.DEPTNO=DEPT.DEPTNO
DROP TABLE TABLE5
DROP TABLE TABLE6
example:TABLE10
COLUMNS:
col1 : 1
col2:
"select * INTO TABLE5 from EMP
SELECT * INTO TABLE6 FROM DEPT
SELECT eno,ename,dname FROM TABLE5,TABLE6 WHERE EMP.DEPTNO=DEPT.DEPTNO
DROP TABLE TABLE5
DROP TABLE TABLE6"
In a query analyzer i have written like this
declare @s-2 varchar(8000)
select @s-2=col2 from TABLE10
exec sp_executesql @s-2
Iam getting the output of eno,name,dname from both the tables(THIS IS THE QUERY WHICH IAM GETTING THE OUTPUT :SELECT eno,ename,dname FROM TABLE5,TABLE6 WHERE EMP.DEPTNO=DEPT.DEPTNO)
I WANT TO insert into a temporary table the output of the above data .
How to insert into a table ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Check out INSERT INTO and SELECT INTO in BOL - they will both insert records into a table.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Also check out the temporary tables and table data type entries in BOL. This may be a good to use the table data type.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Tyson L. Price Mr or Mrs. 500 Points: 589 More actions · Answer 3

There's been times I've wanted to insert the results from a stored procedure into a temporary table also. I don't think there is a way. Maybe someone does????

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

CREATE TABLE #Temp (

... -- columns matching output of stored proc

)

INSERT INTO #Temp

EXEC StoredProcedure @params ...

The limitation is that the stored proc must return only one reordset.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Yates SSCoach Points: 19738 More actions · Answer 5

And you MUST use a proper temp table (starting with #), not a table variable (starting with @)

Aswani kumar Mr or Mrs. 500 Points: 556 More actions · Answer 6

THANK U GUYS FOR YOUR ANSWERS

insert into TEMPORARYTABLE exec sp_executesql @sqlquery

This temporary table will have the definition exactly which is the output of @sqlquery.

LIKE THIS I HAVE DONE

Tyson L. Price Mr or Mrs. 500 Points: 589 More actions · Answer 7

Tyson L. Price

Mr or Mrs. 500

Points: 589

November 2, 2005 at 5:40 am

#601796

Thank you!