Copy tables

Question

Hi all! 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

It's done! Thank you A.Wegierski n also thank you MikeR I appreciate your help. CU. Regards.

Andrzej Wegierski · Answer

You are using MSSQL I think: 1. Create DataView. It could use any database (see help) CREATE VIEW [ . ] [ . ] view_name [ ( column [ ...n ] ) ] 2. Delete rows from local table (use TRUNCATE TABLE ?). 3. Insert rows from view to local table (see help) About Your program: May be table has no rows? Check dsGUTIERREZFull.Tables[TableIndex].Rows.Count

Carlos Sanchez · Answer

Is there a SQL command that do the same but with 2 connections? Well now I'm working with the second way but an exception was thrown. The below is the code i hope you understand it. Exception : An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll Additional information: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. Wrong line: SqlParameter Parameter = new SqlParameter(dsGUTIERREZFull.Tables[TableIndex].Columns[ColumnIndex].ColumnName dsGUTIERREZFull.Tables[TableIndex].Rows[RowIndex] [ColumnIndex].GetType()); Comments: dsGUTIERREZFull is a DataSet that has 8 tables. When the exception is thrown the dataset has the next values: TableIndex = 0 RowIndex = 0 ColumnIndex = 0 dsGUTIERREZFull.Tables.Count = 8 dsGUTIERREZFull.Tables[TableIndex].Rows.Count = 1 dsGUTIERREZFull.Tables[TableIndex].Columns.Count = 10 Code: public void CopyBD() { try { Connection2.Open(); Connection1.Open(); for (int TableIndex=0; TableIndex<dsGUTIERREZFull.Tables.Count; TableIndex++) { SqlCommand cmdDelTable = new SqlCommand(DELETE FROM  + dsGUTIERREZFull.Tables[TableIndex].TableNameConnection2); cmdDelTable.ExecuteNonQuery(); for (int RowIndex=0; RowIndex<dsGUTIERREZFull.Tables [TableIndex].Rows.Count; RowIndex++) { SqlCommand cmdInsCopyTable = new SqlCommand(GetCommandText (TableIndex)Connection2); for(int ColumnIndex=0; ColumnIndex<dsGUTIERREZFull.Tables [TableIndex].Columns.Count; ColumnIndex++) { SqlParameter Parameter = new SqlParameter(dsGUTIERREZFull.Tables [TableIndex].Columns[ColumnIndex].ColumnName dsGUTIERREZFull.Tables[TableIndex].Rows[RowIndex] [ColumnIndex].GetType()); //this line throws the exception Parameter.Value=dsGUTIERREZFull.Tables[TableIndex].Rows [RowIndex][ColumnIndex]; cmdInsCopyTable.Parameters.Add(Parameter); }//for ColumnIndex cmdInsCopyTable.ExecuteNonQuery(); }//for RowIndex }//for TableIndex Connection2.Close(); Connection1.Close(); }//Try catch (SqlException E) { MessageBox.Show(Ha ocurrido un error de conexión con el servidor a continuación se especifica el error o los errores que ocurrieronError MessageBoxButtons.OKMessageBoxIcon.Error); for (int i=0; i<E.Errors.Count; i++) MessageBox.Show(Error :  + E.Errors[i].Message + 
 Origen:  + E.Errors [i].Source + 
 Linea:  + E.Errors[i].LineNumberError MessageBoxButtons.OKMessageBoxIcon.Error); } } public string GetCommandText(int TableIndex) { string CommandText = INSERT INTO  + dsGUTIERREZFull.Tables [TableIndex].TableName +  (; for (int Index=0; Index<dsGUTIERREZFull.Tables[TableIndex].Columns.Count; Index++) { if (Index != 0) CommandText +=  ; CommandText += dsGUTIERREZFull.Tables[TableIndex].Columns [Index].ColumnName; } CommandText += ) VALUES (; for (int Index=0; Index<dsGUTIERREZFull.Tables[TableIndex].Columns.Count; Index++) { if (Index != 0) CommandText +=  ; CommandText += ?; } CommandText += ); return CommandText; }

Andrzej Wegierski · Answer

DELETE FROM database2:table; INSERT INTO database2:table SELECT * FROM database1:table; COMMIT; in Your database language of course. -or- Connect to 1 database. Get data into DataTable (see DataAdapter in help) Connect into 2 database. Delete all table rows with SQL Command. Next load all from DataTable. Check row status on difficulties.

Carlos Sanchez · Answer

I can do that but I want to do it by code cuz is necesary to my application. I have 2 application n 2 DataBase. The firs DataBase is modify by the first application and I have a second application that retrieve the data from the first DataBase n store them in the second DB. any easy idea? thanx.

miker · Answer

I think the best way is to use database tools like DTS in MS SQL Server.