-2

I have to write a query that find missing data, but I want it to write without any subqueries or nested SELECT (it finds the missing data and inserts it from a table to the one that has missing data).

The query is:

INSERT INTO Personal_tbl 
    SELECT * 
    FROM PersonalView 
    WHERE PID NOT IN (SELECT PID FROM Personal_tbl)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Sara Moradi
  • 33
  • 5

2 Answers2

0

You would generally use not exists for such task:

insert into personal_tbl (...) -- enumerate the target columns here
select *                       -- and here as well
from personalview pv
where not exists (select 1 from personal_tbl pt where pt.pid = pv.pid)

I don't see why you wouldn't want to use a subquery for this. With an index on personal_tbl(pid), this should be an efficient approach. But if you like, you can implement the same logic with a left join:

insert into personal_tbl (...)
select pv.*
from personalview pv
left join personal_tbl pt on pt.pid = pv.pid
where pt.pid is null
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I have a problem with the join, it insert the missing data at the end of the table? do u know how i can fix that? – Sara Moradi Dec 28 '20 at 13:18
  • @SaraMoradi: I don't know what you mean. The query inserts the data into the table. There is no notion of beginning or end in the rows of the table. – GMB Dec 28 '20 at 16:19
0
INSERT INTO Personal_tbl
SELECT Pv.*
FROM PersonalView Pv
INNER JOIN Personal_tbl pt ON  pt.PID <> Pv.PID
Dale K
  • 25,246
  • 15
  • 42
  • 71
Sato Takeru
  • 1,669
  • 4
  • 12
  • 27
  • Best practice is to explicitly list the columns being inserted, and to explicitly list the columns being selected. – Dale K Jan 03 '21 at 08:12