SQL Query to insert data to a table

**Ed Murphy** · May 21 '07, 03:35 PM

Re: SQL Query to insert data to a table

albertleng wrote:

Let say i have a table A with the following 3 fields. 1)Date
2)Title 3)Status.

I trust that these aren't the real column names. Celko will likely
come along later today and post his usual lecture.

i need to insert into table B (also have the same 3 fields) from this
table A with the condition where Title is "Alarm" and Status is "ON".
This can be done by a simple "INSERT" query.
>
However, there's a case for table A in like below:
Date Title Status
------ ------ ----------
5/7/07 1:05:23am ALARM ON
5/7/07 1:05:24am ALARM ON
5/7/07 1:05:25am ALARM ACK
5/7/07 1:05:25am ALARM ON
>
Based on the table A above, i only need to insert from table A into
table B the first 2 ALARM(ON)s. The third ALARM(ON) which has the same
Date with ALARM(ACK) needs not to be inserted into table B.
>
How can i write a simple SQL query which can insert all ALARM(ON)s
which doesnt have same date with ALARM(ACK)?

insert into B (Date, Title, Status)
select Date, Title, Status
from A a1
where Title = 'ALARM'
and Status = 'ON'
and not exists (
select *
from A a2
where a2.Date = a1.Date
and a2.Title = a1.Title
and a2.Status = 'ACK'
)

SQL Query to insert data to a table

SQL Query to insert data to a table

Comment