Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-09 : 18:18:59
|
| David writes "Note: This is a restatment of a question I submitted earlier this morning.----------------Sorry if this is a lame question. It involves updating multiple SQL Server records from an external, non SQL Server source. The answer may be very obvious but I clearly lack the vocabulary keywords to find it.Rather than let outside editors directly access information in our main server I use DTS to export records to Access, send them the resulting .MDB file (which has supporting forms) and let them edit that. Then I use DTS to import the corrected records.The editors typically alter more than one field in each record so I need to be able to update the entire record rather than individual fields in the record.Right now I use the Transform option to have DTS first delete all rows in the receiving table. For myriad obvious reasons I'd prefer to have it update existing records with matching primary key values. I could be wrong but I think resorting to Subscriptions, Publications, and Merge Replication might be overkill.Is there a simpler way to import and update records?Thanks.David" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-09 : 18:34:09
|
| You can try this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14248If you don't want to individually update each column, you can use the DELETE-then-INSERT approach. If the column wasn't updated, then there's no harm done. This would only require a simple DTS copy into the staging table, the SQL statements would handle the update process.I don't suppose that the Access databases are online, and you could simply link them to the SQL Server tables? Then you wouldn't have to replicate or even transfer anything. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-10 : 00:26:02
|
| Why not import the records into a staging table like Rob talks about in the thread he linked above, and then just run a large UPDATE statement where Staging.ID = Production.ID listing every field in the SET clause? This may be a resource hog, I don't know for sure, but it sounds simple.------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
Is there such a thing as Bulk UPDATE (like Bulk INSERT)?