How to copy tables?

[ben2]

-------------------------------------------------------------------------------- Hi I have a basic question. I want to copy a table in a db to another db (in the destination the table has to be created first with proper columns). the table name and schema will remain same. (I'm using sql server 2000). Can you provide sample ado.net code, by which I can achieve that? Thanks

[turbochimp]

This question belongs on the ADO/SQL board. Since I wrote the reply to it before stopping to consider that fact, I'll post it here, but in the future, try to keep the questions on-topic, please. Assuming your tables are on the same server instance (otherwise you'll have to further qualify the table objects, link servers etc.): INSERT INTO [new database name].[owner id].[new table name] SELECT * FROM [old database name].[owner id].[old table name] Example: INSERT INTO sampledatabase2.dbo.table1 SELECT * FROM sampledatabase1.dbo.table1 This example uses the '*' wildcard for brevity. You really should list out the columns in your own code. As for what code you might use, I would suggest using a SqlConnection and a SqlCommand (using the ExecuteNonQuery method of the SqlCommand to execute the above syntax). As an alternative to creating the schema by hand, and assuming you have appropriate permissions, you could also do something like: SELECT [old database name].[owner id].[old table name].* INTO [new database name].[owner id].[new table name] FROM [old database name].[owner id].[old table name] Example: SELECT sampledatabase2.dbo.table1.* INTO sampledatabase1.dbo.tablefoo FROM sampledatabase2.dbo.table1 but explicitly creating the schema, or using a script created from the original is safer.

The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’

[Alex Korchemniy]

If you are doing a local replication task I would suggest using DTS or BCP. Alex Korchemniy

[ben2]

This method is fine only when source and destination tables are in the same server. What if they are in different servers?