SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CET + insert into issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwacz23
Starting Member

44 Posts

Posted - 10/29/2013 :  04:11:05  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 10/29/2013 :  04:13:19  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 10/29/2013 :  04:15:56  Show Profile  Reply with Quote
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 - 10/29/2013 :  04:24:03  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 10/29/2013 :  04:43:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/29/2013 :  08:37:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000