Insert row by using loop with condition

Question

I have tbl_emp like that:

empid
1  
2
3
4

And tbl_att like that:

empid     workingdate
1         2017-05-11
2         2017-05-13
3         2017-05-14
...........
...........

I have a job in SQL Server agent to execute step every Sunday and I want that job to insert a row for each empid with that day into tbl_att. Let's say Sunday is 2017-05-22 so I want it like that:

empid     workingdate
1         2017-05-22
2         2017-05-22
3         2017-05-22

It means that I want it to insert into tbl_att for all empid with the same day (task execution day), so can anyone guide me a query that I need to put into my step command?

it's a previous data, and what i want is to insert new row to table for every Sunday, not update the existed record to Sunday bro/sis. — Sok Dymang
– Sok Dymang, Commented May 25, 2017 at 4:19
What insert statement you tried? Did it insert the record? What error did you see? — Chetan
– Chetan, Commented May 25, 2017 at 4:43

Trimantra Software Solution · Accepted Answer · 2017-05-25 04:51:05Z

2

Try this,

INSERT INTO tbl_att SELECT empid,CAST(GETDATE() AS DATE) FROM tbl_emp;

answered May 25, 2017 at 4:51

Trimantra Software Solution

3,3273 gold badges28 silver badges45 bronze badges

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

Comments

JayaPrakash · Accepted Answer · 2017-05-25 04:59:13Z

0

insert into tbl_att (empid, workingdate)
Select empid,cast(getdate() as date) from tbl_emp

As you run the job on every Sunday, above query will insert the data as per your expectation I believe. FYI, But it depends on the system date

answered May 25, 2017 at 4:59

JayaPrakash

1991 silver badge6 bronze badges

4 Comments

Sok Dymang Over a year ago

But can you explain me about this line of query?

JayaPrakash Over a year ago

Getdate will fetch the current system date. As you're running on sunday, it will fetch the Sundays equivalent system date.

Sok Dymang Over a year ago

Thanks you for your explanation

user7715598 Over a year ago

How would you eliminate the duplicate records not to be inserted

user7715598 · Accepted Answer · 2017-05-25 07:42:02Z

0

Hope it helps you

CREATE TABLE #tbl_att (empid INT,workingdate DATE)

CREATE TABLE #EmpidTab (empid INT)

INSERT INTO #EmpidTab
SELECT 1 UNION ALL 
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
SELECT * FROM #EmpidTab

INSERT INTO #EmpidTab
SELECT 5

INSERT INTO #tbl_att
SELECT Empid
    ,Getdate() AS workingdate
FROM #EmpidTab i
WHERE NOT EXISTS (
        SELECT 1
        FROM #tbl_att t
        WHERE t.empid = i.Empid
        ) --Eliminate duplicte insertion of empid's 

SELECT @@ROWCOUNT AS NoRowsInserted

SELECT *
FROM #tbl_att

answered May 25, 2017 at 7:42

user7715598

Collectives™ on Stack Overflow

Insert row by using loop with condition

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related