Insert into output with update

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)

Insert into output with update

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-27 : 07:39:49

How can I know that the INSERT ran so as not to do the update?

INSERT INTO Objects (AccountID)
OUTPUT INSERTED.ID INTO Othertable (ID)
 SELECT AccountID FROM OPENXML (@hdoc, '/Objects/Record', 1) WITH (AccountID int) xmlElements WHERE NOT EXISTS (SELECT ID FROM Objects WHERE Objects.AccountID = xmlElements.AccountID)

--ELSE DO AN UPDATE
 
 UPDATE Objects SET Objects.....

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 07:54:30

read merge in sql 2005/2008 http://db-staff.com/index.php/microsoft-sql-server/66-t-sql-sql-server-2008

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-27 : 07:57:37

Wow!!!

:( Shit I'm using SQL2005

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-27 : 08:03:45

I was trying to do the following. But @@IDENTITY returns the identity of OUTPUT INSERTED.ID INTO Othertable (ID)(correctly) even though it's not doing an insert (incorrectly???):



DECLARE @Inserted int
SET @Inserted = 0 

INSERT INTO Objects (AccountID)
OUTPUT INSERTED.ID INTO Othertable (ID)
 SELECT AccountID FROM OPENXML (@hdoc, '/Objects/Record', 1) WITH (AccountID int) xmlElements WHERE NOT EXISTS (SELECT ID FROM Objects WHERE Objects.AccountID = xmlElements.AccountID)

SELECT @Inserted = @@IDENTITY
 
IF @Inserted = 0
begin
 UPDATE Objects SET Objects.....
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:09:13

what about this?


DECLARE @Inserted int
SET @Inserted = 0 

INSERT INTO Objects (AccountID)
OUTPUT INSERTED.ID INTO Othertable (ID)
 SELECT AccountID FROM OPENXML (@hdoc, '/Objects/Record', 1) WITH (AccountID int) xmlElements WHERE NOT EXISTS (SELECT ID FROM Objects WHERE Objects.AccountID = xmlElements.AccountID)

SELECT @Inserted = @@ROWCOUNT
 
IF @Inserted = 0
begin
 UPDATE Objects SET Objects.....
end

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-01-28 : 09:11:33

Visakh16,

Thanks but doesn't that give me the last iterations @@ROWCOUNT i.e. It gets all the rows from OPENXML processing them all and then moves onto the update?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:23:44

what do you mean by last iteration? you're not looping but just inserting everything by single insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources