4 Replies - 654 Views - Last Post: 29 October 2014 - 01:20 PM Rate Topic: -----

#1 Damage  Icon User is offline

  • Lord of Schwing
  • member icon

Reputation: 284
  • View blog
  • Posts: 1,961
  • Joined: 05-June 08

Check for two part primary key

Posted 28 October 2014 - 04:49 PM

I'm obviously wording this wrong in my searches. I'm trying to insert into a table with a two part primary key, two bigints, id1 and id2.

Is there an easy way to check for this

My current query is

WITH ParentID AS
(
SELECT DISTINCT G1_ASSET_SEQ_NBR,ParentID,G1_ASSET_GROUP,G1_ASSET_TYPE
FROM [Property_SPMComponentDataLinks]  SPM
		INNER JOIN GASSET_MASTER Parent ON Parent.START_VALUE = SPM.Parentid WHERE G1_ASSET_GROUP = 'Property' AND SPM.ParentID <>0
),

ChildID AS
(
SELECT DISTINCT  G1_ASSET_SEQ_NBR,spm.ChildID
FROM [Property] SPM
		INNER JOIN GASSET_MASTER Child ON Child.START_VALUE = SPM.ChildID WHERE G1_ASSET_GROUP = 'Property' AND SPM.ChildID <>0
)

--SELECT DISTINCT 
INSERT INTO gasset_asset 
  SELECT ParentID.G1_ASSET_SEQ_NBR,ChildID.G1_ASSET_SEQ_NBR
       FROM     [Property_SPMComponentDataLinks]  SPM
		INNER JOIN ParentID ON ParentID.ParentID = SPM.ParentID
		INNER JOIN ChildID ON ChildID.ChildID = SPM.ChildID
  WHERE (SPM.PArentID <> 0  AND SPM.ChildId <> 0)




i've thought of trying

WHERE (SPM.PArentID <> 0  AND SPM.ChildId <> 0) and ParentID.G1_ASSET_SEQ_NBR + ' ' + ChildID.G1_ASSET_SEQ_NBR not in (select id1 + ' ' + id2 from gasset_asset)




but then i start running into casting issues.

Is there a more elegant way to check something like that?

Is This A Good Question/Topic? 0
  • +

Replies To: Check for two part primary key

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,058
  • Joined: 08-June 10

Re: Check for two part primary key

Posted 29 October 2014 - 05:59 AM

Quote

Is there a more elegant way to check something like that?

you mean to check if the composite key already exists?

sure, try to do the insert and if the key already exists, the DB will deny the insertion and throw an error.
Was This Post Helpful? 0
  • +
  • -

#3 Damage  Icon User is offline

  • Lord of Schwing
  • member icon

Reputation: 284
  • View blog
  • Posts: 1,961
  • Joined: 05-June 08

Re: Check for two part primary key

Posted 29 October 2014 - 11:57 AM

hah fair enough, but is there a way to do it that allows the insert to continue,without removing the constraint?

I ended up identifying the records that violated the constraint and removed them prior to the insert but i'd still like to know if theres a better way of handling that
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1830
  • View blog
  • Posts: 5,761
  • Joined: 15-January 14

Re: Check for two part primary key

Posted 29 October 2014 - 01:12 PM

Are you looking for something like this?

... WHERE (id1, id2) NOT IN (SELECT id1, id2 FROM ...)

Was This Post Helpful? 1
  • +
  • -

#5 Damage  Icon User is offline

  • Lord of Schwing
  • member icon

Reputation: 284
  • View blog
  • Posts: 1,961
  • Joined: 05-June 08

Re: Check for two part primary key

Posted 29 October 2014 - 01:20 PM

seriously.....that simple....

Awesome i managed to way over-complicate this

Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1