RE : COPYING another table in SQL

[Vektor]

I am need of assistance me and my colleague just thought if it is possible to say we have Created a table included a desc of the table below e.g

DESC regions;

Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NUMBER
REGION_NAME VARCHAR2(25)


NB the regions table has the following values below.

SELECT *
FROM regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa


and say now i want to create a copy of the same exact table but without the values how can i go about it.

The SYNTAX below creates a copy of the same table but the problem is it has values ... Thank u in regards.

CREATE TABLE copy_emp
AS(SELECT * FROM regions);

[Anthro]

Code: Select all

CREATE TABLE copy_emp
AS(SELECT * FROM null);

Do that rather ??

[Vektor]

Code: Select all

CREATE TABLE copy_emp
AS(SELECT * FROM null);

Do that rather ??

That wont work as FROM need to be referenced with a table name only, the null keyword is reserved word

[Anthro]

Whoops . .
I noticed that now . .
I play with sql.. rustypup / SBSP may be able to facilitate better

[Vektor]

Whoops . .
I noticed that now . .
I play with sql.. rustypup / SBSP may be able to facilitate better

LOL ok keep on trying though.

[Anthro]

Why not use DTS

Or sql script . .

Suppose you're trying to copy the authors table of the Employee SQL Server database with data and structure. To do so simply use this SQL script:

Code: Select all

USE Emp
GO

SELECT *
INTO copy_of_Emp
FROM Emp
GO

*edit* wait that is copying data aswell

[Kronos]

EDIT: NO Wait....

Just, Why would you want to do this?

[Anthro]

So it is because he is saying that it must copy data(regions) aswell ?
I am not a sql boffin.. I play around that's all

[Anthro]

* * *

[Vektor]

[quote="Kronos"]EDIT: NO Wait....

Is it possible just a brain teaser a lilttle thought we had at mind and still debating about it.

[rustypup]

i'd need sql flavour andf dbase before making an educated guess that

Code: Select all

CREATE TABLE SomeTable AS (SELECT * FROM A_Table) WHERE 1=2;
or
SELECT INTO SomeTable FROM A_Table WHERE 1=2;

probably does it... i'm sure this is one of those trick interview questions, as it is more likely you'll use one of the DBMS tools, (ie, the correct approach), as the above hack is not gauranteed to work across flavours...

[moreMHZ]

DROP DATABASE;

[SBSP]

If i was you i would just export to Excel/Csv using DTS.

By selecting the source then destination.

Edit in excel then, import again.

This seems like a once off thing.
if you want drop the old table then rename the new one to the old one
saves the hassle, by playing around with inserting data you might even stuff it up by accidentally inserting stuff into the original table.

Unless you are trying to learn SQL syntax.

[AlphA]

I'm with RustyPup on this one

Code: Select all

 create table copy_table as (select * from original_table where 1 = 2;

Thats how I do it in Oracle...

[Vektor]

i'd need sql flavour andf dbase before making an educated guess that

Code: Select all

CREATE TABLE SomeTable AS (SELECT * FROM A_Table) WHERE 1=2;
or
SELECT INTO SomeTable FROM A_Table WHERE 1=2;

probably does it... i'm sure this is one of those trick interview questions, as it is more likely you'll use one of the DBMS tools, (ie, the correct approach), as the above hack is not gauranteed to work across flavours...

Cool that really works created a copy of a table and gave me results with no values. See below

SQL>SELECT *
FROM regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

CREATE TABLE copy_regions
AS (SELECT * FROM regions WHERE 1=2);

Table created.

SQL> DESC copy_regions;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NUMBER
REGION_NAME VARCHAR2(25)

SQL> SELECT *
2 FROM copy_regions;

no rows selected

Thanks in regards.

[Vektor]

i'd need sql flavour andf dbase before making an educated guess that

Code: Select all

CREATE TABLE SomeTable AS (SELECT * FROM A_Table) WHERE 1=2;
or
SELECT INTO SomeTable FROM A_Table WHERE 1=2;

probably does it... i'm sure this is one of those trick interview questions, as it is more likely you'll use one of the DBMS tools, (ie, the correct approach), as the above hack is not gauranteed to work across flavours...

Cool that really works created a copy of a table and gave me results with no values. See below

SQL>SELECT *
FROM regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

CREATE TABLE copy_regions
AS (SELECT * FROM regions WHERE 1=2);

Table created.

SQL> DESC copy_regions;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NUMBER
REGION_NAME VARCHAR2(25)

SQL> SELECT *
2 FROM copy_regions;

no rows selected

Ok now a lil extra why does the 1 = 2 come in. Thanks in regards.

[AlphA]

Check out this link

[Vektor]

Check out this link

Pretty Niffty site helped me out greatly... .
Thanks