CODEWITHSUNDEEP
sqlinsert
Fearghal
Fearghal

Reputation: 11407

Sql Server Insert a new row if 1 value does not exist in dest table

I have 2 tables, A and B. I want to insert the name and 'notInTime' into table B if that name IS NOT currently falling into the period between time_start and time_end.

eg: time now = 10:30

TABLE A
NAME TIME_START (DATETIME) TIME_END (DATETIME)
A 12:00 14:00
A 10:00 13:00
B 09:00 11:00
B 10:00 11:00
C 12:00 14:00
D 16:00 17:00

Table B
Name Indicator
A intime
B intime

If run the query should add the following to Table B

C notInTime
D notinTime

Upvotes: 1

Views: 607

Answers (4)

BitWise
BitWise

Reputation: 78

Much like @trapicki said, but no reason to hardcode the time.

insert
  into b
  (select distinct name,
                   'notInTime'
     from a
     where sysdate not between a.time_start and a.time_end)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

This will add those in time and those not in time to table_b

declare @now time = '10:30'

INSERT INTO TABLE_B(Name, Indicator)
select 
a.NAME, case when b.chk = 1 THEN 'intime' else 'notInTime' end
from 
(
select distinct NAME from TABLE_A
) a
outer apply
(select top 1 1 chk from TABLE_A 
where @now between TIME_START and TIME_END and a.Name = Name) b

Upvotes: 1

trapicki
trapicki

Reputation: 2020

Tried BETWEEN?

  INSERT INTO TABLE B 
 (SELECT DISTINCT
         name
       , 'notInTime'
    FROM A
  WHERE '10:30' NOT BETWEEN TIME(a.TIME_START) AND TIME(a.TIME_END))

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

INSERT INTO TABLE_B b (column_name_1, column_name_2)
SELECT 
'C', 
CASE WHEN EXISTS (SELECT 1 FROM TABLE_A a WHERE a.NAME = 'C' AND '10:30' BETWEEN TIME(a.TIME_START) AND TIME(a.TIME_END)) THEN 'intime' ELSE 'notinTime' END
UNION ALL 
SELECT 
'D', 
CASE WHEN EXISTS (SELECT 1 FROM TABLE_A a WHERE a.NAME = 'D' AND '10:30' BETWEEN TIME(a.TIME_START) AND TIME(a.TIME_END)) THEN 'intime' ELSE 'notinTime' END

Upvotes: 2

Related Questions