Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: How to create a table from an existing table.

    I'm new to this table and my kniwledge of SQL is of intermediate level.
    Could anyone help me/guide me how to create a tble from a nexisting table, without copying any of the records?
    Any help is greatly appreciated.
    Thanks in advance.
    Kalpana

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try Select * into <your new table name> from <your old table name> where 1 = 2
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2003
    Posts
    9

    ThankQ

    ThankQ very much I have been thinking to include a condition which is always false. Good idea!
    Last edited by kalpana_lloyd; 09-09-03 at 08:41.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Just remember that this will only copy the table structure and NOT handle any indexes, statistics or foreign keys.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Sep 2003
    Posts
    9

    another trouble

    Thanks very much Paul.
    Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

    Wishes
    Kalpana

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It's just the fieldnames & data types, so required properties, default settings, all (check, foreign key, primary key) constraints as well as external structures like indices won't be copied.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Re: another trouble

    Originally posted by kalpana_lloyd
    Thanks very much Paul.
    Actually my problem is I'm writing a vb program as part of which I have to apply some query on table1 of db1 and save the results in table2 of db2. As of now I just thought of saving the results in the same database i.e db1. But, my real problem is still unsolved. I wonder if you could help me in this! Any ideas you could give!

    Wishes
    Kalpana
    What have you worked out so far for this problem? Have you figured out how to connect ot your remote server? If you apply the same query on table1 two times what wil lyou call table2 the second time?
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Sep 2003
    Posts
    9
    I have two ADOconnection objects, each of which connects to a different database. My source table in in db1 which I have to filter and save in table2 of db2.
    Each time the query is executed the name of table2 should be changed.
    So, the second time table2 will not be table2, it might change to table3.

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Seems to me you have a few options here.

    1. do your query on table1, get the results back to your app, analyse the resultset for datatypes returned, build a create table command in your app, create the table on db2 and then push the data to db2..table2.

    2. similar to #1 above but extract the table structure directly from db1, build the table on db2 with the appropriate name change then move the data via your app.

    3. Similar to #2 but use bcp to extract the resultset in bulk and then use insert bulk to push data into db2.

    4. if your servers are linked together you could use a four part nameing convention to run a query on db1 stroeing the results on db2. To accomodate the need to chage the target table name you would need to execute dynamic sql.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Sep 2003
    Posts
    9
    Thanks. As of now both of my tables are in the same p.c, but in different databases.
    Im not able to find any objects which could allow me to do these transactions. As of now Im using ADOs. If you could suggest me which objects in ADO support any of these operations I shall be very grateful.

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    okay how about this, The TSQL statment would be something like:


    declare @tblname varchar(50), @tsql varchar(255)
    set @tblname = 'kalpana_lloyd'
    set @tsql = 'select * into db2..' + @tblname + ' from db1..table1 where 1 = 2'
    exec(@tsql)

    This could be sent as one statment in ADO or wrapped in an stored procedure.
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Sep 2003
    Posts
    9

    Talking

    Thanks very much . I shall try this and see.

  13. #13
    Join Date
    Sep 2003
    Location
    Seattle
    Posts
    1
    Hint: You will need 2 connection objects, one for each database, unless both are running under the same instance of sql server.

    Question: Do you have to create the destination table on the fly, or are you just copying data from table to table? If you have to create the new table on the fly, you may need to create some logic to test for an existing table - if you have Query Analyzer, go into the templates and take a look at the script for creating table, it will have a portion starting with an "IF EXISTS" that checks for the existance of the table name you are trying to create, and drops it from the database...

    However, if you are just copying the data over, then the first time you would need to create the table, and each following time you would need an insert rather than "SELECT INTO"

    Good luck!

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if the two dbs are on the same server only one connection is needed.
    Paul Young
    (Knowledge is power! Get some!)

  15. #15
    Join Date
    Sep 2003
    Posts
    9
    Thanks Paul. Actually my tables are in Access database. Because one can use T-SQL in VB using ADO's I have posted this doubt in this forum,. Moreover, I could'nt find any apt forum to post my question. I'm trying all my best , what Paul has told is working fine in SQL Server, now I need to see how I can implement this in VB.Please accept my apologies for I haven't made my doubt clear.
    Thanks.

Posting Permissions

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