CODEWITHSUNDEEP
sql
Jays
Jays

Reputation: 13

copy the tables after execution

I would like to create new table after executing that query

create table newTable as select * from oldTable

However, this does not appear to work. How do I get the new table after executing some queries?

Upvotes: 0

Views: 88

Answers (3)

Chait
Chait

Reputation: 1273

I am not sure what DBMS you are using or what errors you are getting, so I will try to answer for multiple systems.

If you are working with Oracle or PostgreSQL (there might be some other systems that this rule applies to), your syntax seems to be correct. Just make sure your new table doesn't exist yet - otherwise it's going to error out. In case if you are trying to insert into an existing table - which I don't think the case is, however - you can try something like -

INSERT INTO newTable SELECT * FROM oldTable

On the other hand, if you are working with T-SQL (SQL Server), you could SELECT INTO the new table. The new table will be created with the old table's schema.

You can read more about the INTO Clause at MSDN Library.

Your code should look like -

SELECT *
INTO newTable 
FROM oldTable

And, specifying the column names and filters also works the similar way -

SELECT  Column1, Column2, Column3, ...
INTO    newTable 
FROM    oldTable
WHERE   <Filter Condition>

Whatever the case is, you would get more help if you specify the details.

Upvotes: 1

narendra
narendra

Reputation: 21

As You Said you want to copy the values into new table after execution whether if you are running the stored procedure using cursor let the cursor shuld be closed then use query as follows

Select * into Table1 from Table2

if you want to copy selected colums go for

Select Coloumn1 ,column2,... into table1 from table 2 where ............

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

The syntax in general is like:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

For example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
      FROM companies
      WHERE id > 1000);

Try removing the stars (*) and add the brackets.

Read here for more examples.

Upvotes: 1

Related Questions