How to insert rows from one temp table to another in sql

Question

    ID  LogDate      LogTime           InoutMode    
    1   2017-02-23  19:30:00.0000000    1   
    2   2017-02-23  20:00:00.0000000    0   
    3   2017-02-23  20:30:00.0000000    1   
    4   2017-02-23  21:00:00.0000000    0   
    5   2017-02-23  21:30:00.0000000    1   
    6   2017-02-24  08:00:00.0000000    0

The above result got from a temp table.now i want to insert LogTime to another temp table like below.

      InOutMode-1 =>InTime
      InOutMode-0 =>OutTime

     InTime            OutTime
   19:30:00.0000000   20:00:00.0000000
   20:30:00.0000000   21:00:00.0000000
   21:30:00.0000000   08:00:00.0000000

It is already answered check http://stackoverflow.com/a/42665218/7012137, its the same, you just have the date in separate column — Pream, Mar 08 '17 at 11:35

score 1 · Answer 1 · edited Mar 11 '17 at 21:11

1

To insert the row of one table into another table you can write this SQL query:

INSERT INTO anotherTemp (InTime)
    SELECT LogTime
    FROM Temp
    WHERE InoutMode = 1  

INSERT INTO anotherTemp (OutTime)
    SELECT LogTime
    FROM Temp
    WHERE InoutMode = 0

See this link for more http://www.dofactory.com/sql/insert-into

edited Mar 11 '17 at 21:11

marc_s

675,133
158
1,253
1,388

answered Mar 08 '17 at 11:39

Vishal Neekhra

33
1
7

score 0 · Answer 2 · answered Mar 08 '17 at 11:37

0

Use INSERT INTO .. SELECT FROM construct like

insert into #temp2
select ID, LogDate, 
case when InoutMode = 1 then LogTime end as InTime, 
case when InoutMode = 0 then LogTime end as OutTime,
InoutMode    
from #temp1

answered Mar 08 '17 at 11:37

Rahul

71,392
13
57
105

score 0 · Accepted Answer · answered Mar 08 '17 at 12:07

Use 2 cte's, one for InoutMode with 1 and other for 0 and also give row-number based on the order of Id column or date and time columns. Then join these 2 cte's.

Query

;with cte as(
    select [rn] = row_number() over(
        order by [Id] -- also order by [LogDate], [LogTime]
    ), *
    from #your_temp_table_name
    where [InoutMode] = 1
),
cte2 as(
    select [rn] = row_number() over(
        order by [Id] -- also order by [LogDate], [LogTime]
    ), *
    from #your_temp_table_name
    where [InoutMode] = 0
)
select 
    t1.[LogTime] [InTime],
    t2.[LogTime] [OutTime]
from cte t1
left join cte2 t2
on t1.[rn] = t2.[rn];

If you want the result set into a new temp table. Then last part can be modified as

select 
    t1.[LogTime] [InTime],
    t2.[LogTime] [OutTime] 
into #your_new_temp_table_name
from cte t1
left join cte2 t2
on t1.[rn] = t2.[rn];

Find demo here

How to insert rows from one temp table to another in sql

3 Answers3