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 2008 Forums
 Transact-SQL (2008)
 CET + insert into issue

Author  Topic 

kwacz23
Starting Member

44 Posts

Posted - 2013-10-29 : 04:11:05
Hi

COuld you help me below: i can not do insert into table from CET result

with A_table
AS (
SELECT (SELECT ItemId FROM ExtSubscription WITH(NOLOCK) WHERE Id = CHILD.Id) [ChildItemId],
(SELECT id FROM CMDBHardware WITH(NOLOCK) WHERE Contractid = PARENT.Id) [ParentItemId]
FROM StgSubscription CHILD WITH(NOLOCK)
INNER JOIN cmdbcontract PARENT WITH(NOLOCK) ON PARENT.ExternalSystemId = CHILD.ParentSubscriptionNumber

WHERE CHILD.Id IS NOT NULL AND PARENT.Id IS NOT NULL
--AND CHILD.Id <> PARENT.Id
AND NOT EXISTS (SELECT ChildItemId FROM CMDBItemRelationship WITH(NOLOCK) WHERE ChildItemId IN
(SELECT ItemId FROM ExtSubscription WITH(NOLOCK) WHERE Id = CHILD.Id)))

INSERT CMDBItemRelationship (ChildItemId, ParentItemId)
select ChildItemId,ParentItemId from A_table
where [ParentItemId] is not null

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-10-29 : 04:13:19
HI,

-Any error msg?
-the CTE give you rows ? (select * from A_table)
-triggers exists ?




Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-10-29 : 04:15:56
ChildItemId or ParentItemID are related to some field ? from another table (FK)


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

kwacz23
Starting Member

44 Posts

Posted - 2013-10-29 : 04:24:03
Hi
IN fact it started working.But If I put at the beginning statment:

Begin tran

is not working. any ideas?
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-10-29 : 04:43:10
look what is blocking

select * from sys.sysprocesses
where open_tran=1

maybe from your where condition

AND NOT EXISTS (SELECT ChildItemId FROM CMDBItemRelationship WITH(NOLOCK) WHERE ChildItemId IN
(SELECT ItemId FROM ExtSubscription WITH(NOLOCK) WHERE Id = CHILD.Id)))


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-10-29 : 08:37:45
If you put a BEGIN TRAN, you will need either a COMMIT or ROLLBACK at the end.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page
   

- Advertisement -