November 1, 2005 at 7:25 am
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 varchar(8000)
select @S=col2 from TABLE10
exec sp_executesql @S
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 ?
November 1, 2005 at 8:30 am
To insert into a temp table use the format:
select fname, lname, job_desc
into #temp
from employee
join jobs on employee.job_id = jobs.job_id
The INTO #temp creates the temp table and stores the result of the query.
I didn't understand your query. If you write
SELECT eno,ename,dname
FROM TABLE5 ,TABLE6
WHERE EMP.DEPTNO=DEPT.DEPTNO
SQL won't know what emp or dept is. Is table5 and table6 real tables. If so try
SELECT eno,ename,dname
FROM TABLE5 emp,TABLE6 dept
WHERE EMP.DEPTNO=DEPT.DEPTNO
If not use:
SELECT eno,ename,dname
FROM emp,dept
WHERE EMP.DEPTNO=DEPT.DEPTNO
You may find the JOIN syntax easier to work with that is :
SELECT eno,ename,dname
FROM emp
Join dept ON EMP.DEPTNO=DEPT.DEPTNO
or to insert the results in a temp table:
SELECT eno,ename,dname
INTO #temp
FROM emp
Join dept ON EMP.DEPTNO=DEPT.DEPTNO
FROM emp
Join dept ON EMP.DEPTNO=DEPT.DEPTNO
Francis
November 1, 2005 at 10:55 pm
SELECT eno,ename,dname
FROM TABLE5 ,TABLE6
WHERE EMP.DEPTNO=DEPT.DEPTNO
Instead of above this is the write query
SELECT eno,ename,dname
FROM TABLE5 emp,TABLE6 dept
WHERE EMP.DEPTNO=DEPT.DEPTNO
exec sp_executesql @S
when i executed the above statement i will get output ,i want to insert the above statement output into temporary table,not like single statement.
November 2, 2005 at 2:27 am
If I understand you correctly, you are storing your SQL statements in Table10, and retrieving them to the variable @S before executing them with exec sp_executesql.
Is it that you want to retrieve multiple SQL statements from Table10 and direct all the output of these statements to go into a single temporary table?
David
If it ain't broke, don't fix it...
November 2, 2005 at 3:51 am
I believe you want what Francis was talking about, specifically:
SELECT eno,ename,dname
INTO #temp
FROM TABLE5 emp,TABLE6 dept
WHERE EMP.DEPTNO=DEPT.DEPTNO
This will put the results in the temporary table #temp.
To see the contents, simply query the temporary table:
SELECT *
FROM #temp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply