Delete from Table Comparision - 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 2005 Forums

Transact-SQL (2005)

Delete from Table Comparision

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-15 : 07:48:19

Hi,

How to compare between two tables (tbDetails & tbDetails2) -

if vesselName in tbDetails
present

if vesselName in tbDetails2
not present

it will delete vesselName from tbDetails

if row is deleted, it will insert into tbDetailsDeleted.

else row cannot be deleted.


USE [Table123]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertDetailsDeleted]    Script Date: 06/15/2009 19:30:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_InsertDetailsDeleted]
	-- Add the parameters for the stored procedure here
	@vesselName NVarChar (200) = Null,
	@size NVarChar (50) = Null,
	@owner NVarChar (200) = Null,
	@user NVarChar (50) = Null
AS
BEGIN
	SET NOCOUNT ON;

	begin transaction
		Insert into tbDetailsDeleted (VesselName,Size,Owner,Users)
					values
					(@vesselName,@size,@owner,@user);
			if @@error=0
				begin
				Delete from tbDetails where VesselName=@vesselName	
					if @@error=0
							begin
								Commit transaction
							end
						else
							begin
								Rollback transaction
							end
				end
			else
				begin
					Rollback transaction
				end	
END

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-15 : 08:35:30

[code]
try this sample
DECLARE @tbl TABLE (vesselid int, vesselname varchar(32))
INSERT INTO @tbl SELECT 1, 'lll'
INSERT INTO @tbl SELECT 2, 'fff'
INSERT INTO @tbl SELECT 3, 'sd'
INSERT INTO @tbl SELECT 4, 'hrt'

DECLARE @tbl1 TABLE (vesselid int, vesselname varchar(32))
INSERT INTO @tbl1 SELECT 1, 'asdf'
INSERT INTO @tbl1 SELECT 2, 'fff'
INSERT INTO @tbl1 SELECT 3, 'hjkl'
INSERT INTO @tbl1 SELECT 4, 'hrt'

select * from @tbl
select * from @tbl1

declare @tbDetailsDeleted table ( vesselname varchar(32))
--1.method
delete t output deleted.vesselname into @tbDetailsDeleted from @tbl t
left join @tbl1 t1 on t1.vesselname = t.vesselname
where t1.vesselname is null

select * from @tbl
select * from @tbl1
select * from @tbDetailsDeleted

--2. Method
delete t output deleted.vesselname into @tbDetailsDeleted
from @tbl t where NOT EXISTS (select * from @tbl1 where vesselname = t.vesselname)
[/code]

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-15 : 22:11:55

How do i integrate this into the stored prod instead of it in temp table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 00:45:09

The first part of suggestion is only to prepare sample data, because we have no access to your data.
What you need to concentrate on, is the second part that starts after the two SELECTs.

E 12°55'05.63"
N 56°04'39.26"

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-16 : 02:10:50

How do i read deleted.VesselName ?
delete query: delete from tbl where VesselName='Sup vessel'


delete   t  output VesselName = 'Sup Vessel' into  @tbDetailsDeleted  
from @tbl t where NOT EXISTS (select * from @tbl1 where vesselname = t.vesselname)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-16 : 05:49:09

quote:
Originally posted by melon.melon

How do i read deleted.VesselName ?
delete query: delete from tbl where VesselName='Sup vessel'

delete t output VesselName = 'Sup Vessel' into @tbDetailsDeleted
from @tbl t where NOT EXISTS (select * from @tbl1 where vesselname = t.vesselname)

while deleting the records from the tbl (table) insert requiered records in to the table tbldetailsdeleted by output clause

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-16 : 06:28:06

[code]
USE [Table123]
GO
/****** Object: StoredProcedure [dbo].[sp_InsertlDetailsDeleted] Script Date: 06/16/2009 18:05:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: CH
-- Create date:
-- Description: Insert Contact
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertlDetailsDeleted]
-- Add the parameters for the stored procedure here
@vesselName NVarChar (200) = Null,
@size NVarChar (50) = Null,
@owner NVarChar (200) = Null,
@user NVarChar (50) = Null
AS
BEGIN
SET NOCOUNT ON;

begin transaction
Delete @vesselName output deleted.VesselName into tbDetailsDeleted
from tbDetails where Not EXISTS (select * from tbDem where VesselName=@vesselName)
if @@error=0
begin
Commit transaction
end
else
begin
Rollback transaction
end
end
else
begin
Rollback transaction
end
END

[/code]

This is the error it show:
must declare @vesselName
incorrect syntax near else
incorrect syntax near end

kishore_pen
Starting Member

49 Posts

Posted - 2009-06-16 : 06:36:10

use TableDiff.exe built in tool provided by Microsoft.
generally available in location: C:\Program Files\Microsoft SQL Server\90\COM

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-16 : 21:30:21

Thanks, the suggestions were helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources