Insert into select statement using stored procedure in SQL Server

Question

I'm trying to move some columns from one table to another and during this process, I want to convert datetime column as Date and time before inserting into another table. For that, I'm using a stored procedure to apply the condition. But I'm not able to apply condition as such. Below are my tables that I'm using.

dbo.Attendance

Id                                      UserId                                  Status  CheckIn             CheckOut            Date
---------------------------------------------------------------------------------------------------------------------------------------------
0083c28b-249e-4072-b6ac-822d7e4d2d35    5e397451-362f-4e1f-b257-0406fd8da3f8    Late    09:04:00.0000000    00:00:00.0000000    2017-01-16
0146da5c-0eae-4b4f-b9e6-2b5bffb34235    b3e0dc55-f738-478f-87ff-f52dae1f662a    Late    09:33:00.0000000    00:00:00.0000000    2017-01-24
023903d9-b6be-40fa-828e-83bee1748864    5e397451-362f-4e1f-b257-0406fd8da3f8    Full Day    08:45:00.0000000    00:00:00.0000000    2017-02-20
043030e5-fcc2-4409-b804-25fd5cce6a90    366c3918-8569-46ef-aa6c-d95e941bd255    Full Day    09:00:00.0000000    00:00:00.0000000    2017-03-13
05a15650-04c5-49f2-9476-046e15c4d917    b3e0dc55-f738-478f-87ff-f52dae1f662a    Late    09:13:00.0000000    00:00:00.0000000    2017-01-10
09f41fb0-6060-432f-91b6-c53be457f415    b3e0dc55-f738-478f-87ff-f52dae1f662a    Late    09:01:00.0000000    00:00:00.0000000    2017-01-21

dbo.UserActivity

DateTime                   UserId
----------------------------------------------------------------
2017-01-16 17:41:23.670    8c2d617b-8361-432a-9f19-0c70f81764fa
2017-01-31 08:59:22.093    3f8fc164-92e9-4714-a42c-8ae8a2f923a9
2016-09-30 11:41:41.100    da42b0f4-7d0b-4b68-ad81-12bea670c218
2017-01-20 14:19:10.613    8c2d617b-8361-432a-9f19-0c70f81764fa
2017-02-09 17:28:39.373    ac0ce1ad-f021-4fab-ab5e-9bbd48004a4b

Query

CREATE PROCEDURE abc1(@id uniqueidentifier)
AS
BEGIN
    DECLARE 

    INSERT INTO dbo.Attendence (Id, UserId, Date, CheckIn, CheckOut) 
        SELECT  
            Id = @id, UserId, Datetime AS dt, 
            DateTime AS chkin, DateTime AS chkout 
        FROM 
            dbo.UserActivity
END

Where should I convert datetime as time and date separately for Date, checkin and checkout columns in attendance table from user activity table plus id for Attendence table?

Serkan Arslan · Accepted Answer · 2017-11-30 05:38:24Z

2

You can use this in your procedure.

INSERT INTO dbo.Attendence(Id,UserId, Date, CheckIn, CheckOut) 
SELECT 
    NEWID() AS Id
    , UserId
    , CAST([DateTime] AS DATE) as dt
    , CAST([DateTime] AS TIME) as chkin
    , CAST([DateTime] AS TIME) as chkout 
FROM dbo.UserActivity UA
WHERE NOT EXISTS
    ( SELECT * FROM dbo.Attendence A 
        WHERE A.UserId = UA.UserId 
            AND A.Date = CAST(UA.[DateTime] AS DATE) 
            AND A.CheckIn = CAST(UA.[DateTime] AS TIME)
            AND A.CheckOut = CAST(UA.[DateTime] AS TIME) )

edited Nov 30, 2017 at 5:38

answered Nov 30, 2017 at 5:21

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Salar Muhammad Over a year ago

and where should i declare id as newId()?

Serkan Arslan Over a year ago

you can add like NEWID() AS Id

Serkan Arslan Over a year ago

Do you want to use same ID for all rows or unique Id for each row?

Salar Muhammad Over a year ago

unique id for each row plus excluding the rows that already exists records of same UserId and datetime in attendance table

Salar Muhammad Over a year ago

Thank you so much @sarslan :)

Serkan Arslan · Accepted Answer · 2017-11-30 05:30:20Z

0

You can use this.

SELECT Id=NEWID(),
        UserId,
        SELECT CAST(CONVERT(VARCHAR(10),DateTime,111) AS DATE) AS dt,
        SELECT CONVERT(VARCHAR(12), DateTime, 108) AS chkin,
        SELECT CONVERT(VARCHAR(12), DateTime, 108) AS chkout,
FROM dbo.UserActivity

edited Nov 30, 2017 at 5:30

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

answered Nov 30, 2017 at 5:27

Deepak

133 bronze badges

Collectives™ on Stack Overflow

Insert into select statement using stored procedure in SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related