|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sql weirdas select * into #temp from employees exec('select * into #temp from employees') select * from #temp where is the result of or how can i access the result of the exec() statement -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 use pubs
select * into #temp from employee exec('select * into #temp2 from employee select * from #temp2') select * from #temp you could create the table first (script it out), then run the exec, then it should be available http://sqlservercode.blogspot.com/ Show quote "Jose G. de Jesus Jr MCP, MCDBA" wrote: > create proc SQLweird > > as > > select * into #temp from employees > exec('select * into #temp from employees') > select * from #temp > > where is the result of or how can i access the result of the exec() statement > > > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 oh well that's make this weirder.
whats the explanaion behind -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "SQL" wrote: > use pubs > > select * into #temp from employee > exec('select * into #temp2 from employee select * from #temp2') > > select * from #temp > > you could create the table first (script it out), then run the exec, then it > should be available > > http://sqlservercode.blogspot.com/ > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > create proc SQLweird > > > > as > > > > select * into #temp from employees > > exec('select * into #temp from employees') > > select * from #temp > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 Hi
It is not clear why you are wanting to do this. Your temporary table created in the exec statement will not be in scope after the exec statement finishes. You can do: select * into #temp from northwind..employees where EmployeeID <= 5 exec('select * into #temp from northwind..employees where EmployeeID > 5 select * from #temp') select * from #temp drop table #temp If you want to add to the temporary table created at the outer level you can use an insert statement select * into #temp from northwind..employees where EmployeeID <= 5 exec('INSERT INTO #temp ( LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath ) SELECT LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath from northwind..employees where EmployeeID > 5 order by EmployeeID ' ) select * from #temp drop table #temp John Show quote "Jose G. de Jesus Jr MCP, MCDBA" wrote: > create proc SQLweird > > as > > select * into #temp from employees > exec('select * into #temp from employees') > select * from #temp > > where is the result of or how can i access the result of the exec() statement > > > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 thanks
i have a dynamic query on the exec. no of fields will have to change -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "John Bell" wrote: > Hi > > It is not clear why you are wanting to do this. > > Your temporary table created in the exec statement will not be in scope > after the exec statement finishes. > > You can do: > select * into #temp from northwind..employees where EmployeeID <= 5 > exec('select * into #temp from northwind..employees where EmployeeID > 5 > select * from #temp') > select * from #temp > drop table #temp > > If you want to add to the temporary table created at the outer level you can > use an insert statement > > select * into #temp from northwind..employees where EmployeeID <= 5 > exec('INSERT INTO #temp ( LastName, FirstName, Title, TitleOfCourtesy, > BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, > Extension, Photo, Notes, ReportsTo, PhotoPath ) > SELECT LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, > Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, > Notes, ReportsTo, PhotoPath from northwind..employees where EmployeeID > 5 > order by EmployeeID ' ) > select * from #temp > drop table #temp > > John > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > create proc SQLweird > > > > as > > > > select * into #temp from employees > > exec('select * into #temp from employees') > > select * from #temp > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 Hi
The need to do this tends to suggest poor design, ubt you could use a global temporary table select * into #temp from northwind..employees where EmployeeID <= 5 exec('select * into ##temp2 from northwind..employees where EmployeeID > 5 ') select * from #temp select * from ##temp2 drop table #temp drop table ##temp2 John Show quote "Jose G. de Jesus Jr MCP, MCDBA" wrote: > thanks > > i have a dynamic query on the exec. no of fields will have to change > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 > > > "John Bell" wrote: > > > Hi > > > > It is not clear why you are wanting to do this. > > > > Your temporary table created in the exec statement will not be in scope > > after the exec statement finishes. > > > > You can do: > > select * into #temp from northwind..employees where EmployeeID <= 5 > > exec('select * into #temp from northwind..employees where EmployeeID > 5 > > select * from #temp') > > select * from #temp > > drop table #temp > > > > If you want to add to the temporary table created at the outer level you can > > use an insert statement > > > > select * into #temp from northwind..employees where EmployeeID <= 5 > > exec('INSERT INTO #temp ( LastName, FirstName, Title, TitleOfCourtesy, > > BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, > > Extension, Photo, Notes, ReportsTo, PhotoPath ) > > SELECT LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, > > Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, > > Notes, ReportsTo, PhotoPath from northwind..employees where EmployeeID > 5 > > order by EmployeeID ' ) > > select * from #temp > > drop table #temp > > > > John > > > > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > > > create proc SQLweird > > > > > > as > > > > > > select * into #temp from employees > > > exec('select * into #temp from employees') > > > select * from #temp > > > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > > > > > -- > > > thanks, > > > > > > ------------------------------------ > > > Jose de Jesus Jr. Mcp,Mcdba > > > Data Architect > > > Sykes Asia (Manila philippines) > > > MCP #2324787 What you need to remember is,
Any local temporary table is deleted once it's scope becomes invalid. So if you do an insert inside a exec (select * into), it is destroyed once the scope goes outside the exec. the way around this is to do the following: --- create the table structure, (with no records) select TOP 0 * into #temp from employee select * from #temp --- proove it has no records --- put the records in using exec exec('insert into #temp select * from employee') --- get the results out select * from #temp Show quote <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message news:E1B142FA-5849-491C-8B83-AE4F6BA27528@microsoft.com... > create proc SQLweird > > as > > select * into #temp from employees > exec('select * into #temp from employees') > select * from #temp > > where is the result of or how can i access the result of the exec() statement > > > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 I need to clarify that..
Any local temporary table is deleted once the scope it was created in becomes invalid. Show quote "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message news:4358f385$0$137$7b0f0fd3@mistral.news.newnet.co.uk... > What you need to remember is, > > Any local temporary table is deleted once it's scope becomes invalid. > > So if you do an insert inside a exec (select * into), it is destroyed once > the scope goes outside the exec. > > the way around this is to do the following: > > > --- create the table structure, (with no records) > select TOP 0 * into #temp from employee > select * from #temp --- proove it has no records > > --- put the records in using exec > exec('insert into #temp select * from employee') > --- get the results out > select * from #temp > > > > > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message > news:E1B142FA-5849-491C-8B83-AE4F6BA27528@microsoft.com... > > create proc SQLweird > > > > as > > > > select * into #temp from employees > > exec('select * into #temp from employees') > > select * from #temp > > > > where is the result of or how can i access the result of the exec() > statement > > > > > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 > > clap... clap... clap...
thanks -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "Rebecca York" wrote: > What you need to remember is, > > Any local temporary table is deleted once it's scope becomes invalid. > > So if you do an insert inside a exec (select * into), it is destroyed once > the scope goes outside the exec. > > the way around this is to do the following: > > > --- create the table structure, (with no records) > select TOP 0 * into #temp from employee > select * from #temp --- proove it has no records > > --- put the records in using exec > exec('insert into #temp select * from employee') > --- get the results out > select * from #temp > > > > > <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message > news:E1B142FA-5849-491C-8B83-AE4F6BA27528@microsoft.com... > > create proc SQLweird > > > > as > > > > select * into #temp from employees > > exec('select * into #temp from employees') > > select * from #temp > > > > where is the result of or how can i access the result of the exec() > statement > > > > > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 > > > now thats only part one
use pubs select * into #temp from employee exec('select * into #temp2 from employee) --- dynamic query here no. of fields ------------------------------------------------------changes select * from #temp2 <<(i want this huhuhu) -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "Jose G. de Jesus Jr MCP, MCDBA" wrote: > create proc SQLweird > > as > > select * into #temp from employees > exec('select * into #temp from employees') > select * from #temp > > where is the result of or how can i access the result of the exec() statement > > > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 can you do the whole thing in dynamic sql?
like this exec('select * into #temp2 from employee do some more processing here select * from #temp2') or script the table out beforehand by going to the information_scheme.columns table and looping thru all the columns http://sqlservercode.blogspot.com/ Show quote "Jose G. de Jesus Jr MCP, MCDBA" wrote: > now thats only part one > > > use pubs > > select * into #temp from employee > exec('select * into #temp2 from employee) --- dynamic query here no. of fields > ------------------------------------------------------changes > select * from #temp2 <<(i want this huhuhu) > > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > create proc SQLweird > > > > as > > > > select * into #temp from employees > > exec('select * into #temp from employees') > > select * from #temp > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 disregard the statement about scripting the table beforehand, that would be
separate also Show quote "SQL" wrote: > can you do the whole thing in dynamic sql? > like this > exec('select * into #temp2 from employee > do some more processing here > select * from #temp2') > > or script the table out beforehand by going to the > information_scheme.columns table and looping thru all the columns > > > http://sqlservercode.blogspot.com/ > > > > > > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > now thats only part one > > > > > > use pubs > > > > select * into #temp from employee > > exec('select * into #temp2 from employee) --- dynamic query here no. of fields > > ------------------------------------------------------changes > > select * from #temp2 <<(i want this huhuhu) > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 > > > > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > > > create proc SQLweird > > > > > > as > > > > > > select * into #temp from employees > > > exec('select * into #temp from employees') > > > select * from #temp > > > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > > > > > -- > > > thanks, > > > > > > ------------------------------------ > > > Jose de Jesus Jr. Mcp,Mcdba > > > Data Architect > > > Sykes Asia (Manila philippines) > > > MCP #2324787 so it would be
create #temp with one colum select *into #temp1 from employees exec( select * into temp2 from employee alter table from outer scope temp select * from temp2 into temp ) select from temp many many many thanks.... -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "SQL" wrote: > can you do the whole thing in dynamic sql? > like this > exec('select * into #temp2 from employee > do some more processing here > select * from #temp2') > > or script the table out beforehand by going to the > information_scheme.columns table and looping thru all the columns > > > http://sqlservercode.blogspot.com/ > > > > > > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > now thats only part one > > > > > > use pubs > > > > select * into #temp from employee > > exec('select * into #temp2 from employee) --- dynamic query here no. of fields > > ------------------------------------------------------changes > > select * from #temp2 <<(i want this huhuhu) > > > > -- > > thanks, > > > > ------------------------------------ > > Jose de Jesus Jr. Mcp,Mcdba > > Data Architect > > Sykes Asia (Manila philippines) > > MCP #2324787 > > > > > > "Jose G. de Jesus Jr MCP, MCDBA" wrote: > > > > > create proc SQLweird > > > > > > as > > > > > > select * into #temp from employees > > > exec('select * into #temp from employees') > > > select * from #temp > > > > > > where is the result of or how can i access the result of the exec() statement > > > > > > > > > > > > -- > > > thanks, > > > > > > ------------------------------------ > > > Jose de Jesus Jr. Mcp,Mcdba > > > Data Architect > > > Sykes Asia (Manila philippines) > > > MCP #2324787 You need to use
exec('select * into #temp from employees select * from #temp') Madhivanan  |
|||||||||||||||||||||||
Other interesting topics