NOT IN on two columns - SQL Server Forums

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)

NOT IN on two columns

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2012-05-28 : 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

264 Posts

Posted - 2012-05-28 : 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

30421 Posts

Posted - 2012-05-28 : 08:04:15

[code]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[/code]

N 56°04'39.26"
E 12°55'05.63"

mattt
Posting Yak Master

194 Posts

Posted - 2012-05-28 : 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 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources