Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
281 views
in Technique[技术] by (71.8m points)

Rewrite query for SQL Server from Oracle

I have query, help me rewrite it for SQL Server:

insert into swi (co, na, ci, ac, id, version, add) 
    select co, na, ci, acc, id, ?, address 
    from swi_tmp 
    where co||me not in (select co||me from swi)

Now, I have, but it still not working

insert into swi (co, na, ci, ac, id, version, add) 
    select co, na, ci, acc, id, ?, address 
    from swi_tmp 
    where not exists (select 1 
                      from swi_tmp
                      where swi_tmp.co = swi.co and swi_tmp.na = swi.na)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The FROM clause in the NOT EXISTS subquery is using the source table instead of the target table. Change the table name in the NOT EXISTS subquery to the target table name:

INSERT INTO swi (co, na, ci, ac, id, version, add) 
    SELECT co, na, ci, acc, id, ?, address 
    FROM swi_tmp 
    WHERE NOT EXISTS (SELECT 1 
                      FROM swi
                      WHERE
                          swi_tmp.co = swi.co
                          AND swi_tmp.na = swi.na
                     );

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...