Bulk Load DataTable into External Database Table

[Bella_Yu]

I call a data adapter and store results in DataTable in below snippets. How can I bulk load OneStream data in this DataTable to external database table? External database connection is ready and working in below method.

Using dbConnApp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, externalConnectinoName)
brapi.Database.ExecuteSql(dbConnApp, sqlQuery, False)

Using ds As DataSet = brapi.Dashboards.Process.GetAdoDataSetForAdapter(si, isSystemLevel, workspaceID, adapterName, resultsTableName, dctVars)
					
	If ds.Tables.Count > 0 Then ' If there are >0 tables in the results
	
		Using dt As DataTable = ds.Tables(0).Copy()
			'Remove unsed columns
			dt.Columns.Remove("Cube")
			dt.Columns.Remove("TransformationRuleProfile")
			
			For Each Col As DataColumn In dt.Columns
				BRApi.ErrorLog.LogMessage(si, Col.ColumnName)
			Next
			
			
		
		Return dt 
	
		End Using
	End If
							
								
End Using

[Krishna]

@Bella_Yu  - Here is an example in C# but you need to tweak. This will understand the code.

// DataTable Variable
				string tm = "TEMPDB";
				
				//string WFP = |WFProfile|;
			
				// Create a new SqlConnection for source database
				DbConnInfo SrcExtConn = BRApi.Database.CreateExternalDbConnInfo(si,SrcConn);
				// Creating the data table
				DataTable dataTable = new DataTable(tm);
				
				// Executing the SQL from the source table
				dataTable = BRApi.Database.ExecuteSql(SrcExtConn,sql,false);
				
				//Add WFScenario,WFProfile,WFTime to Data Table
			    DataColumn WFProfileName = new DataColumn("WFProfileName",typeof(string));
				WFProfileName.DefaultValue = BRApi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk).Name;
				//WFProfileName.DefaultValue = "PLP_US.PeoplePlanning_Employee_Register";
				dataTable.Columns.Add(WFProfileName);
				
				DataColumn WFT = new DataColumn("WFT",typeof(string));
				WFT.DefaultValue = BRApi.Finance.Time.GetNameFromId(si,si.WorkflowClusterPk.TimeKey);
				dataTable.Columns.Add(WFT);

				
				DataColumn WFS = new DataColumn("WFS",typeof(string));
				WFS.DefaultValue = "PLP_Budget";
				dataTable.Columns.Add(WFS);

// Establishing the connection to the Application DB 
					DbConnInfo TgtExtConn = BRApi.Database.CreateApplicationDbConnInfo(si);
	               	// Convert the connection to String
					string FtgtExtConn = TgtExtConn.ConnectionString;
					// Establishing the Dest Connection
					using (SqlConnection connection = new SqlConnection(FtgtExtConn))
					{ 
						connection.Open();
						// Create the reader object to read the data from data table.
						using (DataTableReader reader = dataTable.CreateDataReader())
						{
							// Create sqlbulkcopy object to copy the data from source to target
							using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
	    					{
						        // Set the destination table name
						        bulkCopy.DestinationTableName = desttable;

						        // Map the DataTable columns to the destination table columns
						            bulkCopy.ColumnMappings.Add("RegisterID", "RegisterID");
						        	bulkCopy.ColumnMappings.Add("RegisterIDInstance", "RegisterIDInstance");
								    bulkCopy.ColumnMappings.Add("WFProfileName", "WFProfileName");
									bulkCopy.ColumnMappings.Add("WFS", "WFScenarioName");
									bulkCopy.ColumnMappings.Add("WFT", "WFTimeName");
										

						        // Copy the data from the DataTable to the destination table
						        bulkCopy.WriteToServer(reader);
	    					}			
		
						 }
						}	

[Bella_Yu]

Thanks, I'll give it a try!

[sameburn]

Hi @Bella_Yu 

You can use this BRApi sub.  There is a boolean variable we can pass in for Bulk Insert (datatable to sql table)