sql query 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql query 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 17:27:51

Please help...
I want a query which should Insert the data in table @t4 when there is no record exists.
If it exists then it should update that data or record.

Please let me know if i am not clear.

-- Prepare sample data
Declare @T1 table (tcol1 Int, tcol2 Int, tcol3 varchar(100), tcol4 datetime)

Insert into @T1
Select 5572, 1, 'test1', '2003-01-04 00:00:00.000' Union all
Select 5572, 1, 'test1', '2003-01-04 00:00:40.000' Union all
Select 5573, 1, 'tes4', '2003-01-05 00:00:00.000' Union all
Select 5574, 1, 'tes5', '2003-01-06 00:00:00.000' Union all
Select 5575, 1, 'tes6', '2003-01-06 00:00:00.000' Union all
Select 5576, 1, 'tes7', '2003-01-06 00:00:00.000' union all
Select 5577, 1, 'tes8', '2003-01-06 00:00:00.000'

Declare @T2 table (t2col1 Int, t2col2 Int, t2col3 Datetime, t2col4 varchar(100))
Insert into @T2
Select 5572, 2, '2003-01-04 00:00:00.000', 'Mouse' Union all
Select 5572, 2, '2003-01-04 00:00:58.000', 'desk' Union all
Select 5573, 2, '2003-01-05 00:00:00.000', 'Mousepad' Union all
Select 5574, 2, '2003-01-06 00:00:00.000', 'Hw' union all
Select 5575, 2, '2003-01-06 00:00:00.000', 'Laptop' union all
Select 5577, 2, '2003-01-06 00:00:00.000', 'Laptop'

Declare @T3 Table (t3col1 Int, t3col2 Int, t3col3 Int, t3col4 Datetime )
Insert into @T3
Select 5572, 3, 09, '2003-01-04 00:00:00.000' Union all
Select 5572, 3, 10, '2003-01-04 00:00:32.000' Union all
Select 5573, 3, 40, '2003-01-05 00:00:00.000' Union all
Select 5574, 3, 65, '2003-01-06 00:00:00.000' union all
Select 5576, 3, 76, '2003-01-06 00:00:00.000'

QUERY:
Declare @T4 table(t4col1 int,t4col2 varchar(100),t4col3 varchar(100),t4col4 INT, t4col5 datetime)
Insert into @T4
select t1.tcol1, t1.tcol3, t2.t2col4, t3.t3col3, t1.tcol4
from @T1 t1 left join @T2 t2
on t1.tcol1 = t2.t2col1
and t2.t2col3 = (select max(t2col3) from @T2 x where x.t2col1 = t1.tcol1)
left join @T3 t3
on t1.tcol1 = t3.t3col1
and t3.t3col4 = (select max(t3col4) from @T3 x where x.t3col1 = t1.tcol1)
where t1.tcol4 = (select max(tcol4) from @T1 x where x.tcol1 = t1.tcol1)
order by t1.tcol1

select * from @T4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 18:18:32

is this the same as http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86311 ?

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 20:26:06

With below query it is able to deduplicate the data and load the data into @T4 table.
I want to modify query which should validate the data by checking if tcol1id values already exists in @t4 table.If exists then it should update the data instead of insert.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 22:08:09

quote:
Originally posted by sqldba2k6

With below query it is able to deduplicate the data and load the data into @T4 table.
I want to modify query which should validate the data by checking if tcol1id values already exists in @t4 table.If exists then it should update the data instead of insert.

You will need a separate query for the update. For the insert, you can use not exists() to check for existence of records in @t4 in your insert query

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 22:31:30

how shuld i modify the query to get the desired output with if not exists....
Thanks for your help in advance!!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 22:39:51

quote:
I want to modify query which should validate the data by checking if tcol1id values already exists in @t4 table.If exists then it should update the data instead of insert.

Please be consistent in your column naming. I can't find where is tcol1id


AND not exists (select * from @t4 x where x.t4col1  = t1.tcol1)

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-12 : 23:46:21

Thanks KH!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources