Creating a table in SQL using values of two table with some

Question

creating a table in SQL using values of two table with some hard coded values.
i want to insert punchin and puchout for each employee_code which i will get from [AttendanceCorrection].[dbo].[Tbl_FMOEmp] and Punch_Date from [Zultime].[dbo].[TIME_SHEET]

What I have tried:

INSERT INTO Tbl_EmpNotPunching
select
(SELECT [employee_Code] FROM [AttendanceCorrection].[dbo].[Tbl_FMOEmp]) as employee_Code,
(SELECT distinct [Punch_Date] FROM [Zultime].[dbo].[TIME_SHEET]) as Punch_Date,
'7:30' as PunchIN,
'16:30' as punchOUT

Nitin Sontakke · Answer

I think you need this. Not sure of course please review. declare @punch_date table ( [punch_date] date ) insert into @punch_date select distinct [Punch_Date] FROM [Zultime].[dbo].[TIME_SHEET] INSERT INTO Tbl_EmpNotPunching select [employee_Code] [punch_date] '07:30' as PunchIN '16:30' as punchOUT FROM [AttendanceCorrection].[dbo].[Tbl_FMOEmp] cross join @punch_date You can always comment the line 9 and see the result BEFORE you insert. I am almost certain you want all dates for all employees and that's what cross join achieves

jack R · Answer

Hi i am using two insert into statment in single stored procedure this stored procedure will execute automatically For first insert into statment i am getting all rows for second insert statment i am receiving zero rows because of the condition where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail) . Can anyone guide me that i should get only one row for each date for each Pin_Code without repeatation when executing stored procdeure multiple times???? Create proc [dbo].[sp_UpdateEmpPunchingDetailTbl] as begin --First insert statement INSERT INTO Tbl_EmpPunchingDetail select Pin_Code as Pin_Code P_Date as P_Date IN1 as IN1 OUT1 as OUT1 from [Zultime].[dbo].[TIME_SHEET] where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail) AND P_Date --Second insert statement DECLARE @mydate DATETIME SELECT @mydate = GETDATE() declare @P_Date table ( [P_Date] date ) insert into @P_Date select distinct [P_Date] FROM [Zultime].[dbo].[TIME_SHEET] where [P_Date] BETWEEN CONVERT(VARCHAR(25)DATEADD(dd-(DAY(@mydate)+8)@mydate)101) AND CONVERT(VARCHAR(25)DATEADD(dd-(DAY(@mydate)-22)@mydate)101) INSERT INTO Tbl_EmpPunchingDetail select [Pin_Code] P_Date '07:30' as [IN1] '16:30' as [OUT1] FROM [AttendanceCorrection].[dbo].[Tbl_FMOEmp] cross join @P_Date where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail ) AND P_Date end

Suvendu Shekhar Giri · Answer

You can use JOINS to make your task easier. You haven't shared the structure of your tables so it's hard to suggest anything. Are you getting any error with what you have tried so far?