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 |
MatthewB
Starting Member
5 Posts |
Posted - 2014-01-24 : 15:30:52
|
I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting isselect distinct qf.[eform_id], qf.[name], qf.[description]from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qfinner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_defaultwhere not exists (select fr.eform_idfrom forms_ref frinner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id); It is outputting nothing, but I have intentionally deleted one form record to force it to show up. Any ideas?Thank you! |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-01-24 : 16:33:04
|
One way to find rows that exist in one table but not another is with this (left outer join where null) model:select ar.<colList>from tableWithAllRows arLEFT OUTER JOIN tablewithSomeRows sr on sr.<correlationCols> = ar.<correlationCols>where sr.<correlationCol> IS NULL just as a warning: JOINs across linked servers can be extremely inefficientBe One with the OptimizerTG |
|
|
MatthewB
Starting Member
5 Posts |
Posted - 2014-01-24 : 17:56:46
|
Thanks TG. I do look for opportunities to make this data pull more efficient, and this is just one element of it. So far, I have not been able to find a better method to limit the data than creating a join with the linked server, but I am open to any ideas.I will change the script as you have outlined. I appreciate the assistance! |
|
|
MatthewB
Starting Member
5 Posts |
Posted - 2014-01-24 : 18:17:06
|
Having said that...would it be more efficient to build a temporary table on the remote server prior to these operations, and use that as a means of isolating data from the other tables? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-27 : 07:29:00
|
quote: Originally posted by MatthewB Having said that...would it be more efficient to build a temporary table on the remote server prior to these operations, and use that as a means of isolating data from the other tables?
yep. that would definitely help. Also make sure you add proper indexes on it like columns used for joining etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|