May 15, 2008 at 12:31 pm
How insert values from TABLE2 into @TempTable if a row into TABLE1 not in TABLE2?
--Query:
DECLARE @TempTable (IdTemp int, TempDate datetime)
INSERT INTO @TempTable (IdTemp, TempDate)
SELECT T1.Id, MAX(T2.Date2)
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.Id = T2.Id
GROUP BY T1.Id
/*
Example:
TABLE1 Id = '1'
TABLE2 = Id '1' not exists
*/
May 15, 2008 at 12:43 pm
--Query:
DECLARE @TempTable (IdTemp int, TempDate datetime)
INSERT INTO @TempTable (IdTemp, TempDate)
SELECT T1.Id, MAX(T2.Date2)
FROM Table1 T1
Left JOIN Table2 T2
ON T1.Id = T2.Id
GROUP BY T1.Id
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 15, 2008 at 12:47 pm
create table TABLE1 (IdTemp int, TempDate datetime)
create table TABLE2 (IdTemp int, TempDate datetime)
insert TABLE1 select 1, '2008-01-01'
insert TABLE2 select 1, '2008-01-01'
insert TABLE2 select 2, '2008-01-02'
DECLARE @TempTable TABLE (IdTemp int, TempDate datetime)
INSERT INTO @TempTable (IdTemp, TempDate)
SELECT T2.IdTemp,T2.TempDate
FROM TABLE2 T2 LEFT JOIN TABLE1 T1
ON T2.IdTemp = T1. IDTemp and T2.TempDate=T1.TempDate
WHERE T1.IDTemp is NULL and T1.Tempdate IS NULL
SELECT * FROm @TempTable
DROP TABLE TABLE2
DROP TABLE TABLE1
Regards
Shrikant Kulkarni
May 15, 2008 at 1:24 pm
Tks for both. Worked fine!
May 15, 2008 at 2:59 pm
I know you have a satisfactory solution but SQL Server 2005 has a new word EXCEPT
as in
SELECT * FROM TableA EXCEPT
SELECT * FROM TableB
gives you everything in Table A not in Table B
very useful
of course, if you know certain columns won't match, compare a subset of each table - using either temp tables or table variables
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply