How to copy tables?

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.

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Carlos Sanchez22y ago
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();
0
S_Kiryazov22y ago
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.
0