Results 1 to 10 of 10

Thread: create table

  1. #1
    Join Date
    Feb 2003
    Posts
    27

    Unanswered: create table

    I am trying to copy the table using "create table"

    CREATE TABLE TABLE1 AS
    SELECT * FROM TABLE0

    I am not able to execute this query. Tried to use the same query in SQLServer 2000 and got error. Is something wrong with the syntax?. Can anybody help me?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    46
    I may be corrected here, but I think you will need to use 2 steps.

    1) create a new table

    CREATE TABLE TableName (FieldName, FieldType);

    2) Then use an INSERT INTO statement to append the desired records.

    Cheers
    zambezibill

  3. #3
    Join Date
    Feb 2003
    Posts
    27
    What you say is correct, but you can also use

    CREATE TABLE TABLE1 AS
    SELECT * FROM TABLE0..

    to create a new table "TABLE0" as "TABLE1" and copy it's contents. i worked on this query earlier. Now i am getting error executing this query.

    "SYNTAX ERROR NEAR CREATE TABLE STATEMENT"

  4. #4
    Join Date
    Feb 2003
    Posts
    14
    you might want to try:
    select * into table1 from table0

  5. #5
    Join Date
    Feb 2003
    Posts
    27
    In that case first , i got to execute queries in the following order

    1. CREATE TABLE TABLE1.
    2. SELECT * INTO TABLE1 FROM TABLE0

    Query
    CREATE TABLE TABLE1
    AS SELECT * FROM TABLE0
    would create the table and as well copy the data.i have executed the same query in oracle and that works fine. i am having problem running it from Microsoft Access and SQL Server.

    Thanks

  6. #6
    Join Date
    Feb 2003
    Posts
    27
    i even tried

    create table table1
    select * into table1 from table0

    It doesn't work

  7. #7
    Join Date
    Feb 2003
    Posts
    14
    get rid of the create table alltogether. when you do:
    select * into table0 from table1
    in SQL server, this statement would both create the table and populate it at the same time.
    HTH

  8. #8
    Join Date
    Feb 2003
    Posts
    27
    i executed "select * into table1 from table0". it gave error message

    "Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option."

    i think i got to run a stored procedure .

  9. #9
    Join Date
    Feb 2003
    Posts
    14
    logon to your database as dbo or sa and issue the following statement and then try again.

    sp_dboption DatabaseName,'select into/bulkcopy',true

    just change the database name to the database you use

  10. #10
    Join Date
    Feb 2003
    Posts
    27
    "SELECT * INTO TABLE1 FROM TABLE0" worked in SQL Server and Microsoft Access. Thank you .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •