Seeking: Insert a table data into an existing table

fujun1982

I put the calculated data into table A (a lot of data), and then inserted the data of A into database table B. A is a table defined in the program, which has the same structure as table B, and there are two columns as primary keys. If there are records in the B table, do not insert (or update is also possible). Inserting one by one takes too much time (large amount of data), plus network transmission, which is not very desirable. Can it be implemented using stored procedures? Can you pass a table in the past? Then background processing? Or something else? ? ? ?

mydriver

UPDATE B
SET B.value_col1=A.value_col1
,B.value_col2=A.value_col2
...
FROM A, B
WHERE B.key_col1=A.key_col1
AND B.key_col2=A.key_col2

- Insert non-existent
INSERT INTO B
(col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM A
WHERE NOT EXISTS
(SELECT 1
FROM B
WHERE B.key_col1=A.key_col1
AND B.key_col2=A.key_col2)

mydriver

- Update already existing
UPDATE B
SET B.value_col1=A.value_col1
,B.value_col2=A.value_col2
...
FROM A, B
WHERE B.key_col1=A.key_col1
AND B.key_col2=A.key_col2

- Insert non-existent
INSERT INTO B
(col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM A
WHERE NOT EXISTS
(SELECT 1
FROM B
WHERE B.key_col1=A.key_col1
AND B.key_col2=A.key_col2)

zzgtgpaul

By the way, you can write directly to the B table when calculating, it is also inserted when there is no update

恶魔右指

Poster
Is it like this
insert a (id, num)-calculated column inserted into table A
select id, sum(num) from t

update b
set num=a.num
from a where b.id=a.id

insert b--insert if not
select * from a where not exists(select 1 from b where id=a.id)

knightzk

select a.id id1,b.id id2 into #t from a left join b on a.id = b.id

insert into b (column)
select column from a inner join #t c on a.id = c.id1 where c.id2 is null

update b
set **********************
from a,#t c
where a.id = c.id1 and c.id2 is not null

undong

Use insert into select from where to exclude unmatched data from the insert

jedivampire

Can use Integration Services to do

116657396

Plus is network transmission
-------------------------------------------------- ----------------------
If the two tables A and B are on different servers, use the linked server method to do it.

As you have described before, you only need no more than two SQL statements to solve this problem.

ykkevin

If it is divided into two places, it is best to transfer the A list to the server where the B list is located.

According to your needs, insert should be enough
--- Copy A to local
select into #A from [....].A

insert B(col1,col2...)
select A.col1,A.col2...
from #A A left join B on A.id = B.id
where B.id is null

For update operations with a large amount of data, if you want to use transactions, remember to do Update first and then Insert, because if the Update goes wrong, the rollback time will be quite long.

If you update a large amount of data, you have to consider the impact of the index on the table on the update efficiency. If you do batches every day and do not affect the business, you can rename table B, and then table B and #A as Union inserts. A table C, rename the table A after rebuilding the index on the table C.