You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have 2 databases that I am working with, and I need to update data in DB1 from the data residing in DB2.
My current method I've been using (prior to discovering this library) was to simply drop the table in DB1 and insert from DB2 to essentially recreate it altogether. The problem with this is due to the table size, this is quite time-consuming (sometimes >2 minutes depending on the computer's speed and connection to the remote db)
Back when I initially was trying to get this accomplished, I saw a bunch of talk about how you can loop through the columns and perform a where statement to update just the ones that need updating, which is what I'd like to implement if possible. (I gave up since there i was pressed for time but now have some time to try and improve.)
This site pretty much details what I want to do, but I am wondering how to achieve that using this library.
Here is my current string to perform the insertion, which works.
SELECT = $"SELECT {ColumnList} INTO [{LocalName}] FROM [{ConnectionString}].[{LinkedTableName}]";
I couldn't figure out how to translate that into SqlKata.
Below is a working query that have generating and running inside of a for-loop to update the local table from a remote table, column by column
**edited with working query string for MSAccess, which is what I'm building for - Note that I have already created a compiler for it, but may need to do further tweaks if sqlkata can come close to buliding the above statement
This is the closes I could get, which doesn't do the job:
Query:
Query SQL = new Query($"{LocalName} as L");
SQL = SQL.Join($"[{ConnectionString}].[{LinkedTableName}] as R", $"L.{PKey}", $"R.{PKey}");
SQL = SQL.AsUpdate($"L.{col}", $"R.{col}");
SQL = SQL.WhereNot($"R.{col}", $"L.{col}");
Compiled: "UPDATE [tblRobot] AS [L] SET [L].[ArrivalDate] = 'R.ArrivalDate' WHERE NOT ([R].[ArrivalDate] = 'L.ArrivalDate')"
it threw out the join statement altogether. It also converted what should be a reference to a column R.[ArrivalDate] to a value. I could use WhereRaw here, but there is no UpdateRaw or any update that accepts a column reference (as far as I can tell)
The text was updated successfully, but these errors were encountered:
I have 2 databases that I am working with, and I need to update data in DB1 from the data residing in DB2.
My current method I've been using (prior to discovering this library) was to simply drop the table in DB1 and insert from DB2 to essentially recreate it altogether. The problem with this is due to the table size, this is quite time-consuming (sometimes >2 minutes depending on the computer's speed and connection to the remote db)
Back when I initially was trying to get this accomplished, I saw a bunch of talk about how you can loop through the columns and perform a where statement to update just the ones that need updating, which is what I'd like to implement if possible. (I gave up since there i was pressed for time but now have some time to try and improve.)
This site pretty much details what I want to do, but I am wondering how to achieve that using this library.
Here is my current string to perform the insertion, which works.
SELECT = $"SELECT {ColumnList} INTO [{LocalName}] FROM [{ConnectionString}].[{LinkedTableName}]";
I couldn't figure out how to translate that into SqlKata.
Below is a working query that have generating and running inside of a for-loop to update the local table from a remote table, column by column
**edited with working query string for MSAccess, which is what I'm building for - Note that I have already created a compiler for it, but may need to do further tweaks if sqlkata can come close to buliding the above statement
This is the closes I could get, which doesn't do the job:
Query:
Compiled:
"UPDATE [tblRobot] AS [L] SET [L].[ArrivalDate] = 'R.ArrivalDate' WHERE NOT ([R].[ArrivalDate] = 'L.ArrivalDate')"
it threw out the join statement altogether. It also converted what should be a reference to a column
R.[ArrivalDate]
to a value. I could useWhereRaw
here, but there is noUpdateRaw
or any update that accepts a column reference (as far as I can tell)The text was updated successfully, but these errors were encountered: