Insert Update scripts - 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)

Insert Update scripts

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-15 : 23:29:47

I need to do is if the insert is successfull imported to tableD run the update statement and if the insert fails at any point the update statment should fail.
Please Correct my query to get the desired output.
Thanks for your help in advance!!!!!!!


SET dateformat dmy
DECLARE @TableA TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
       status  char(3)
)
INSERT INTO @TableA
SELECT	'7-6-2007', 100, 1, 52, 26,'N' UNION ALL
SELECT	'8-6-2007', 100, 1, 52, 47,'N' UNION ALL
SELECT	'7-6-2007', 101, 1, 54, 27,'N' UNION ALL
SELECT	'8-6-2007', 102, 1, 52, 28,'N' UNION ALL
SELECT	'8-6-2007', 105, 1, 62, 38,'N' UNION ALL
SELECT	'8-6-2007', 106, 1, 62, 38,'N'

DECLARE @TableB TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
status  char(3)
)
INSERT INTO @TableB
SELECT	'7-6-2007', 100, 2, 'PR', 'RP1','N'	UNION ALL
SELECT	'8-6-2007', 101, 2, 'PR', 'SP1','N'	UNION ALL
SELECT	'10-6-2007', 103, 2, '0p', 'DT1','N'

DECLARE @TableC TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	FT1 	varchar(2),
	FT2	varchar(2),
status  char(3)
)

INSERT INTO @TableC
SELECT	'7-6-2007', 100, 3, 'KR', '23','N'	UNION ALL
SELECT	'7-6-2007', 101, 3, '1R', '21','N'	UNION ALL
SELECT	'8-6-2007', 102, 3, 'TR', '31','N'	UNION ALL
SELECT	'9-6-2007', 102, 3, 'TP', '31','N'	UNION ALL
SELECT	'11-6-2007', 104, 3, 'Rp', 'VT','N'


DECLARE @TableD TABLE
(
	Pdate 	datetime,
	SID 	int,
	type 	int,
	Er 	int,
	Ac	int,
	type2 	int,
	nt1 	varchar(2),
	nt2	varchar(3),
	type3 	int,
	FT1 	varchar(2),
	FT2	varchar(2)


       
)


IF EXISTS (select SID = coalesce(a.SID, b.SID, c.SID) from @TableA A,@TableB B,@TableC c where SID not in (select SID from @tableD ))
Begin
Begin transaction


INSERT INTO @TableD

SELECT	Pdate	= a.Pdate,
	SID	= coalesce(a.SID, b.SID, c.SID),
	a_type	= a.type, 	a.Er, 		a.Ac,
	b_type	= b.type, 	b.nt1, 		b.nt2,
	c_type	= c.type, 	c.FT1, 		c.FT2
FROM	(
		SELECT	Pdate, SID, type, Er, Ac
		FROM	@TableA t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableA x WHERE x.SID = t.SID)
                and status='N'
               AND not exists (select * from @tabled D where D.sid  = t.SID)
	) a 


	FULL OUTER JOIN --@TableB b

(
		SELECT	Pdate, SID, type, nt1, nt2
		FROM	@Tableb t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableB x WHERE x.SID = t.SID)
                and status='N'
                AND not exists (select * from @tabled D where D.sid  = t.SID)
	) b
	ON	a.SID	= b.SID

	FULL OUTER JOIN --@TableC c
(
		SELECT	Pdate, SID, type, Ft1, Ft2
		FROM	@Tablec t
		WHERE	Pdate	= (SELECT MAX(Pdate) FROM @TableC x WHERE x.SID = t.SID)
                and status='N'
		AND not exists (select * from @tabled D where D.sid  = t.SID)
	) C


	ON	a.SID	= c.SID

ORDER BY SID
 
/* If the data is imported to table4 from three tables(TablesA,TableB,TableC) then status in three tables of respective records should be updated to 'IMP'  */

 UPDATE @tableA t
 SET status = 'IMP' WHERE status='N'
               AND exists (select * from @tableD D where D.sid  = t.SID)

 UPDATE @tableB t
 SET status = 'IMP' WHERE status='N'
               AND exists (select * from @tableD D where D.sid  = t.SID)

 UPDATE @tableC t
 SET status = 'IMP' WHERE status='N'
               AND exists (select * from @tableD D where D.sid  = t.SID)

 --IF (@@Error == 0)  
 --commit
 --else
 --BEGIN
 --rollback transaction
--put other error handling scripts here

END
select * from @tablea
select * from @tableb
select * from @tablec
select * from @tabled

Expected Output:

TableA:

Pdate                                                  SID         type        Er          Ac          status 
------------------------------------------------------ ----------- ----------- ----------- ----------- ------ 
2007-06-07 00:00:00.000                                100         1           52          26          IMP  
2007-06-08 00:00:00.000                                100         1           52          47          IMP  
2007-06-07 00:00:00.000                                101         1           54          27          IMP  
2007-06-08 00:00:00.000                                102         1           52          28          IMP  
2007-06-08 00:00:00.000                                105         1           62          38          IMP  
2007-06-08 00:00:00.000                                106         1           62          38          IMP  



TableB:

Pdate                                                  SID         type        nt1  nt2  status 
------------------------------------------------------ ----------- ----------- ---- ---- ------ 
2007-06-07 00:00:00.000                                100         2           PR   RP1  IMP  
2007-06-08 00:00:00.000                                101         2           PR   SP1  IMP  
2007-06-10 00:00:00.000                                103         2           0p   DT1  IMP


TableC:

Pdate                                                  SID         type        FT1  FT2  status 
------------------------------------------------------ ----------- ----------- ---- ---- ------ 
2007-06-07 00:00:00.000                                100         3           KR   23   IMP  
2007-06-07 00:00:00.000                                101         3           1R   21   IMP  
2007-06-08 00:00:00.000                                102         3           TR   31   IMP  
2007-06-09 00:00:00.000                                102         3           TP   31   IMP  
2007-06-11 00:00:00.000                                104         3           Rp   VT   IMP


TableD:

Pdate                                                  SID         type        Er          Ac          type2       nt1  nt2  type3       FT1  FT2  
------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ---- ---- ----------- ---- ---- 
2007-06-08 00:00:00.000                                100         1           52          47          2           PR   RP1  3           KR   23
2007-06-07 00:00:00.000                                101         1           54          27          2           PR   SP1  3           1R   21
2007-06-08 00:00:00.000                                102         1           52          28          NULL        NULL NULL 3           TP   31
NULL                                                   103         NULL        NULL        NULL        2           0p   DT1  NULL        NULL NULL
NULL                                                   104         NULL        NULL        NULL        NULL        NULL NULL 3           Rp   VT
2007-06-08 00:00:00.000                                105         1           62          38          NULL        NULL NULL NULL        NULL NULL
2007-06-08 00:00:00.000                                106         1           62          38          NULL        NULL NULL NULL        NULL NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources