Dynamically Insert single record into MS access from SQL server
-
Hi ALL, I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database). My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database. Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset. Problem: Insert into Access tables???? i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically. Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables. -------This is My code------------------------------------------------------- private void InsertMsiClientIntoTest(string ClientId) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection); sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text); sqlDataAdapter.Fill(sqlserverDataset); GetDataFromTablesForID(sqlserverDataset); InsertAllTableDataIntoAccess(sqlserverDataset,tableArray); } private DataSet GetDataFromTablesForID(DataSet dsTablesList) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; string tableName = string.Empty; string QueryText = string.Empty; int i =0; tableArray = new string[dsTablesList.Tables[0].Rows.Count]; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); foreach (DataRow itemRow in dsTablesList.Tables[0].Rows) { tableArray[i] = itemRow[0].ToString(); i++; } foreach (string tableItem in tableArray) { tableName = tableItem; QueryText = "select x.* from" + " " + tableName + " " + "x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId"; } sql
-
Hi ALL, I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database). My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database. Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset. Problem: Insert into Access tables???? i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically. Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables. -------This is My code------------------------------------------------------- private void InsertMsiClientIntoTest(string ClientId) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection); sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text); sqlDataAdapter.Fill(sqlserverDataset); GetDataFromTablesForID(sqlserverDataset); InsertAllTableDataIntoAccess(sqlserverDataset,tableArray); } private DataSet GetDataFromTablesForID(DataSet dsTablesList) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; string tableName = string.Empty; string QueryText = string.Empty; int i =0; tableArray = new string[dsTablesList.Tables[0].Rows.Count]; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); foreach (DataRow itemRow in dsTablesList.Tables[0].Rows) { tableArray[i] = itemRow[0].ToString(); i++; } foreach (string tableItem in tableArray) { tableName = tableItem; QueryText = "select x.* from" + " " + tableName + " " + "x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId"; } sql
If you have two datasets 1) from MSSQL 2) from MS Access and all column names and table names match you should be able to iterate the datatables in the datasets and us importrow method to copy a row from one to the other and then use data adapter to execute update command and should use the assigned insert/update commands assigned to the data adapter to populate the data into the database.
-
Hi ALL, I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database). My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database. Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset. Problem: Insert into Access tables???? i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically. Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables. -------This is My code------------------------------------------------------- private void InsertMsiClientIntoTest(string ClientId) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection); sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text); sqlDataAdapter.Fill(sqlserverDataset); GetDataFromTablesForID(sqlserverDataset); InsertAllTableDataIntoAccess(sqlserverDataset,tableArray); } private DataSet GetDataFromTablesForID(DataSet dsTablesList) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; string tableName = string.Empty; string QueryText = string.Empty; int i =0; tableArray = new string[dsTablesList.Tables[0].Rows.Count]; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); foreach (DataRow itemRow in dsTablesList.Tables[0].Rows) { tableArray[i] = itemRow[0].ToString(); i++; } foreach (string tableItem in tableArray) { tableName = tableItem; QueryText = "select x.* from" + " " + tableName + " " + "x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId"; } sql
-
Hi ALL, I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database). My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database. Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset. Problem: Insert into Access tables???? i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically. Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables. -------This is My code------------------------------------------------------- private void InsertMsiClientIntoTest(string ClientId) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection); sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text); sqlDataAdapter.Fill(sqlserverDataset); GetDataFromTablesForID(sqlserverDataset); InsertAllTableDataIntoAccess(sqlserverDataset,tableArray); } private DataSet GetDataFromTablesForID(DataSet dsTablesList) { SqlConnection sqlConnection = null; SqlDataAdapter sqlDataAdapter = null; string tableName = string.Empty; string QueryText = string.Empty; int i =0; tableArray = new string[dsTablesList.Tables[0].Rows.Count]; DataSet sqlserverDataset = new DataSet(); sqlserverDataset.Tables.Add(); sqlConnection = new SqlConnection(); sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;"); sqlConnection.Open(); foreach (DataRow itemRow in dsTablesList.Tables[0].Rows) { tableArray[i] = itemRow[0].ToString(); i++; } foreach (string tableItem in tableArray) { tableName = tableItem; QueryText = "select x.* from" + " " + tableName + " " + "x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId"; } sql
You have no reason to have all the data in memory at once so use a DataReader, not a DataSet. 0) ExecuteReader on the SELECT command 1) Iterate the DataReader's columns to create the INSERT command and its parameters 2) Read from the DataReader 3) Set the INSERT command's parameter values 4) ExecuteNonQuery 5) Repeat from 2 as necessary You should also use a try/catch and a transaction.