| Author |
 Topic  |
|
|
mattt
Posting Yak Master
194 Posts |
 Posted - 05/28/2012 : 07:39:01
|
Hi,
If you have a table with two primary keys:
CREATE TABLE [AttributeMap](
[intParentAttributeId] [bigint] NOT NULL,
[intChildAttributeId] [bigint] NOT NULL,
[datCreatedDate] [datetime] NOT NULL
CONSTRAINT [PK_AttributeMap] PRIMARY KEY CLUSTERED
(
[intParentAttributeId] ASC,
[intChildAttributeId] ASC
)
If you want to do an insert into/select statement to add data to the table, how can you restrict the data to make sure it doesn't violate both keys?
So if you insert this into the table above:
INSERT INTO [AttributeMap] VALUES (1, 1, getdate())
INSERT INTO [AttributeMap] VALUES (1, 2, getdate())
INSERT INTO [AttributeMap] VALUES (1, 3, getdate())
INSERT INTO [AttributeMap] VALUES (2, 1, getdate())
How could you run this query without violating the keys?
declare table @temp (intParent int, intChild int)
insert into @temp (1, 1)
insert into @temp (1, 2)
insert into @temp (4, 4)
insert into @temp (5, 5)
insert into AttributeMap (intParentAttributeId, intChildAttributeId, datCreatedDate)
select intParent, intChild, getDate()
from @temp
So AttributeMap should end up with two new rows, values 4, 4, "date" and 5, 5 "date". Make sense?
Cheers,
Matt |
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 05/28/2012 : 07:50:45
|
Try something like this:
INSERT INTO AttributeMap(intParentAttributeId, intChildAttributeId, datCreatedDate)
SELECT intParent, intChild, GETDATE()
FROM @temp AS T
WHERE NOT EXISTS(SELECT *
FROM AttributeMap AS AM
WHERE AM.intParentAttributeId = T.intParent
AND AM.intChildAttributeId = T.intChild)
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/28/2012 : 08:04:15
|
DECLARE @AttributeMap TABLE
(
intParentAttributeID BIGINT NOT NULL,
intChildAttributeID BIGINT NOT NULL,
datCreatedDate DATETIME NOT NULL,
PRIMARY KEY
(
intParentAttributeID ASC,
intChildAttributeID ASC
)
);
INSERT @AttributeMap
VALUES (1, 1, '20000101'),
(1, 2, '20010101'),
(1, 3, '20020101'),
(2, 1, '20030101');
DECLARE @Temp TABLE
(
intParent INT,
intChild INT
);
INSERT @Temp
VALUES (1, 1),
(1, 2),
(4, 4),
(5, 5);
-- SwePeso
MERGE @AttributeMap AS tgt
USING @Temp AS src ON src.intParent = tgt.intParentAttributeID
AND src.intChild = tgt.intChildAttributeID
WHEN NOT MATCHED BY TARGET
THEN INSERT (
intParentAttributeID,
intChildAttributeID,
datCreatedDate
)
VALUES (
src.intParent,
src.intChild,
GETDATE()
);
SELECT *
FROM @AttributeMap
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 05/28/2012 : 08:57:11
|
| Thanks guys. I had no idea it was so easy, was overcomplicating it in my head. Sometimes it pays just to try the obvious and see if it works :) |
|
|
| |
Topic |
|
|
|
NOT IN on two columns
