[depricated]

The where clause I put together for this is monstrous and I detest it and there HAS GOT to be a better way

Merge won't work (I think) because the target table has one additional column and I need to not delete data that isn't in the source table

So I have about 20 different identical databases that I pull data from. (This is a vendor system, I did not set that up) I iterate through each one and collect everything out of it into a single DB with an additional column to note the source DB.

Right now it's super messy. Truncate the table in the target DB, then iterate through each source DB to refill. Takes a long time, and we're repeating the load every time it runs. I'd rather update existing rows and insert new ones.

So far this has sped it up exponentially on the tables I've written out. However, I'm running into a snag. This might be a silly thing for me to get hung up on, I don't know, but I haven't gotten it yet. Maybe it's just because it's late in the day, I don't know.

The table in question has 2 primary keys: which we'll call C and S

my update works fine as

update t
set t.field1 = src.field1
   ,t.field2 = src.field2
   ,t.etc = source.etc
from target t
   inner join source src
      on  t.C = src.C
      and t.S = src.S
      and t.db = @db

But I need the select equivalent of that. Neither C nor S are strictly unique. They're a unique pair - so you can't have C=10 S=10 in multiple rows, but you could have one row be C=10 S=11, another be C=10 S=12, and another be C=11, S=11

My goal is something along the lines of:

insert into target (columns)
select columns 
from source
where (paired keys) not in (select (paired keys) from target)

How can I value pair these keys, I guess?

[modi123_1]

Seems, off hand, the truncate/insert would be fairly quick.

INSERT into #TableAASDF(id, sourceid sval_
SELECT id, 1, sVal
FROM TABLE1
...
INSERT into #TableAASDF(id, sourceid sval_
SELECT id, 2, sVal
FROM TABLE2
...
INSERT into #TableAASDF(id, sourceid sval_
SELECT id, 3, sVal
FROM TABLE3

..etc.. Only thing changing would be the table source id in each INSERT.

That isn't working well enough?

[modi123_1]

Alternatively you can use 'EXCEPT' on the keys, and use that subquery in a JOIN.

create table #foo(ID1 int, ID2 int, sval varchar(10))
create table #bar(ID1 int, ID2 int, sval varchar(10))
 -- ALTER TABLE #foo ADD  PRIMARY KEY (ID1,ID2)

insert into #foo(ID1,ID2, sval ) values(1, 1, 'asdf')
insert into #foo(ID1,ID2, sval ) values(1, 2, 'dgh')
insert into #foo(ID1,ID2, sval ) values(2, 2, '546541')
insert into #foo(ID1,ID2, sval ) values(2, 3, 'yerty')


insert into #bar(ID1,ID2, sval) values(1, 2, '546541')
insert into #bar(ID1,ID2, sval) values(2, 2, '546541')

select *  from #foo

select *  from #bar

PRINT 'use EXCEPT to get the keys from one that are not in teh other'
	SELECT ID1, ID2
	from #foo 

	except 

	SELECT ID1, ID2
	from #bar 

print 'join up against that sub query to get the rest of #foo'
SELECT a.*
from #foo a
JOIN (
	SELECT ID1, ID2
	from #foo 

	except 

	SELECT ID1, ID2
	from #bar 
) b on a.ID1 = b.ID1 and a.ID2 = b.ID2

drop table #bar
drop table #foo

ID1         ID2         sval
----------- ----------- ----------
1           1           asdf
1           2           dgh
2           2           546541
2           3           yerty

(4 rows affected)

ID1         ID2         sval
----------- ----------- ----------
1           2           546541
2           2           546541

(2 rows affected)

use EXCEPT to get the keys from one that are not in teh other
ID1         ID2
----------- -----------
1           1
2           3

(2 rows affected)

join up against that sub query to get the rest of #foo
ID1         ID2         sval
----------- ----------- ----------
1           1           asdf
2           3           yerty

[depricated]

That's several million rows, and we're not exactly operating on modern equipment. The job currently takes a couple hours. Also, since it dumps the data, if there's an error on the vendor's side that leaves the source DB empty (which seems to happen about once a month) we have to reload the data from a backup after the job truncates it. It's more for the latter reason than the former that I need to not truncate the tables. Making it faster is just a secondary goal, like I'm rewriting it anyway so might as well do it well.

Obviously it's weighing my mind, I'm at home dwelling on it at 9pm lol. I'll take a look with except tomorrow and see if I can get it working with that.