How to copy tables?

Question

Hi everybody! I have the same DataBase in 2 servers (the struct), but one is empty n the other have data, my question is: How to copy an entire table or several tables (the data) from one DataBase to the other Database, by code? Is there a easy way? thanks in advance. Regards.

Carlos Sanchez · Answer

Thank you Stefan, I appreciate yout help, but I have 2 questions: - In the command, how to know what is the target DataBase? - This command works in the analyzer, but it can works in C# by code? e.g. SqlCommand MyCommand = new SqlCommand ("select 'INSERT INTO [dwdb].[dbo]. [UserTypes]([UserTypeID], [Description]) VALUES(' + Convert(varchar(20),UserTypeID) + ', ' + Description + ')' from UserTypes", MyConnection); MyCommand.ExecuteNonQuery();

S_Kiryazov · Answer

We're talking about MS Sql server, right? I don't think that there is an easy way in .net 1.1. With 2.0 along comes the SqlBulkCopy that servers this purpose but by now the easiest way that I have thought of is the following: 1. In the Query Analyzer Object Browser right click the table that you want to copy and choose "Script Object to New Window as" "Insert" the result is, for example: INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description]) VALUES( , ) for a table with two columns: UserTypeID,int and Description,varchar(50) 2. You wrap the generated insert statement into SELECT FROM, like this: SELECT 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description]) VALUES( , )' FROM UserTypes 3. Replace the autogenerated column name-type pairs with string concatenation, like this: select 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description]) VALUES(' + UserTypeID + ', ' + Description + ')' from UserTypes We're almost ready. The only thing left is convertion to varchar for the int fields, e.g. UserTypeID is to become Convert(varchar(20),UserTypeID) The final result is: select 'INSERT INTO [dwdb].[dbo].[UserTypes]([UserTypeID], [Description]) VALUES(' + Convert(varchar(20),UserTypeID) + ', ' + Description + ')' from UserTypes When you run it agianst the original database you get the script that copies the data. It's strange that this is not built in the SQL Server. This data scripting funcitionality is available even in MySQL. I hope that you don't get somehow insulted by my detailed explanation but I have no idea of your MS SQL Server skills and also this might be useful to others. I hope it wil be of some help.

How to copy tables?

Answers (2)