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.
Kalpanatry Select * into <your new table name> from <your old table name> where 1 = 2|||ThankQ very much I have been thinking to include a condition which is always false. Good idea!|||Just remember that this will only copy the table structure and NOT handle any indexes, statistics or foreign keys.|||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|||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.|||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?|||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.|||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.|||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.|||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.|||Thanks very much . I shall try this and see.|||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!|||if the two dbs are on the same server only one connection is needed.|||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.|||Thanks Paul. Your suggestion has just worked even with Access databases, though both of my databases are in different locations.
This is what I used in VB
"Select * into mailshot.mdb.." & txtTableName & " from Contacts " & strWhereClause
I have been struggling for this since one week. Thanks again.
Kalpana

Originally posted by kalpana_lloyd
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.