Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
CREATE TABLE/VIEW from stored procedure or SELECT...

الإجابة CREATE TABLE/VIEW from stored procedure or SELECT...

  • Thursday, June 08, 2006 2:04 PM
     
     
    Sign In to Vote
    0

    Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

    I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

    CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

    Thank you!

     

All Replies

  • Thursday, June 08, 2006 3:02 PM
     
     Answered
    Sign In to Vote
    0
    Look at SELECT ... INTO command.
     
  • Thursday, June 08, 2006 3:36 PM
     
     
    Sign In to Vote
    0

    use northwind

    select  * into #tablex from employees

    select * from #tablex

     
  • Friday, June 09, 2006 10:19 AM
     
     
    Sign In to Vote
    0

    Sorry joeydj but your example create a copy of another table! I need to create a table that contains only a few columns from another table, so that why I need to use a SELECT or a stored procedure that build and execute a SELECT.

    Can I do that?

    Thanks!

     
  • Friday, June 09, 2006 10:24 AM
     
     
    Sign In to Vote
    0
    Sorry gavrilenko_s but I miss your post! You are right! That is the solution! Thanks!
     
  • Friday, June 09, 2006 3:03 PM
     
     
    Sign In to Vote
    0

    hi,

    first you have to create a table that has a similar

    structure with the Sp

    and then you can use

    insert into temp

    exec sp1

    here's a sample snippet


    USE NORTHWIND
    select 'my name.........................12345' as productname, 10000.00000
     as unitprice, 10000.0000 as quantiTY,
    10000.0000 as discount, 10000.0000 as extendedprice
    into tempx

    truncate table tempx

    insert into tempx
    exec dbo.CustOrdersDetail '10248'

    select * from tempx
    drop table tempx

    also suggest you make use of UDFs

    cheers :)