quote:

---

Originally posted by visakh16

INSERT INTO Table
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
                  WHERE PK = q.PK)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

---

quote:

---

Originally posted by immad



Another way is to use a LEFT OUTER JOIN:

INSERT INTO [dbo].[YourTable] ( <Your Column List> )
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL
----------------------------------------------------------

(33 row(s) affected)

(58 row(s) affected)

(66 row(s) affected)

its giving me that result

i want to insert data and show data rows same time is that possible

immad uddin ahmed

---

INSERT INTO Table
OUTPUT INSERTED.*
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
WHERE PK = q.PK)

INSERT INTO [dbo].[YourTable] ( <Your Column List> )
OUTPUT INSERTED.*
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL

quote:

---

Originally posted by chbala85

CREATE TABLE attend_log
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

CREATE TABLE ATTEND_LOG_test
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

insert into attend_log values(12,getdate(),'Remarks',getdate(),GETDATE())
insert into ATTEND_LOG_test values(21,getdate(),'Remarks',getdate(),GETDATE())


insert into ATTEND_LOG (EID,DATE,REMARKS,TIMEIN,TIMEOUT)
select A.EID,A.DATE,A.REMARKS,A.TIMEIN,A.TIMEOUT from ATTEND_LOG_test A
LEFT OUTER JOIN ATTEND_LOG B
ON A.EID=B.EID
WHERE B.EID IS NULL



select * from ATTEND_LOG

---

...
CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) as excesshorttime,
case when (t4.minute - Time_Minutes) > 0 then 'Short'
when (t4.minute - Time_Minutes) < 0 then 'Excess'
else NULL end as ExcessShort,
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT' 
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY' ELSE '' 
END AS Remarks,
..

quote:

---

Originally posted by immad

spent time issue is solve


immad uddin ahmed

---

(
@empid nvarchar(10)
)
as
begin
select * from attend_log 
where eid=@empid
end

CREATE TABLE #TempEmployees
(
--columns with datatypes here (same as resultset of at procedure)
)

INSERT INTO #TempEmployees
EXEC at '26187',''

SELECT * FROM #TempEmployees
--end

alter procedure [dbo].[pocc]
(
@empid nvarchar(10),
@department varchar(10)
)
as
begin
create TABLE #TempEmployees
(
date datetime,
eid int,
remarks varchar(50)
)
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.at @empid,@department

--Verify the Insert records
SELECT eid,
SUM(CASE WHEN remarks = 'OFF DAY' THEN 1 ELSE 0 END) AS offday,
SUM(CASE WHEN remarks = 'ABSENT' THEN 1 ELSE 0 END) AS absent,
SUM(CASE WHEN remarks = 'Late' THEN 1 ELSE 0 END) AS late,
SUM(CASE WHEN remarks = 'HALFDAY' THEN 1 ELSE 0 END) AS halfday
FROM #TempEmployees where eid=@empid
GROUP BY eid
end