problem creating table with multiple unique column

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)

problem creating table with multiple unique column

Author

Topic

sorvani
Starting Member

1 Post

Posted - 2010-05-31 : 19:05:28

I have data currently in a table PossibleMatches represented below by the table variable @PossibleMatches. Based on the sort order in the query below, I am trying to insert each row into the table variable @BestMatches with the requirement that neither the InID nor the OutID can be duplicated individually.

I posted this last week over at [url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d3d6a66b-5ec9-4114-b0eb-9dafdd232a4c[/url], but I haven't had any luck with getting this solved.

I am assuming that I am simply missing something with how unique requirements work on tables.


DECLARE @PossibleMatches AS TABLE(
	[UniqueID] [bigint] NOT NULL,
	[Car] [char](2) NOT NULL,
	[City] [char](3) NOT NULL,
	[EType] [char](3) NOT NULL,
	[IDDate] [date] NOT NULL,
	[InID] [char](4) NOT NULL,
	[InTime] [char](4) NOT NULL,
	[OutID] [char](4) NOT NULL,
	[OutTime] [char](4) NOT NULL,
	[TotalTime] [int] NOT NULL,
	PRIMARY KEY CLUSTERED (UniqueID)
)
INSERT INTO @PossibleMatches (UniqueID,Car,City,EType,IDDate,InID,InTime,OutID,OutTime,TotalTime)
VALUES (7,'US','CLT','319','2010-07-05','1430','1920','1007','1959',39),
	(15,'US','CLT','319','2010-07-05','1777','1916','1007','1959',43),
	(8,'US','CLT','319','2010-07-05','1463','1911','1007','1959',48),
	(18,'US','CLT','319','2010-07-05','1855','1911','1007','1959',48),
	(6,'US','CLT','319','2010-07-05','1400','1908','1007','1959',51),
	(17,'US','CLT','319','2010-07-05','1794','1906','1007','1959',53),
	(14,'US','CLT','319','2010-07-05','1773','1855','1007','1959',64),
	(1,'US','CLT','319','2010-07-05','1047','1835','1007','1959',84),
	(2,'US','CLT','319','2010-07-05','1060','1822','1007','1959',97),
	(12,'US','CLT','319','2010-07-05','1669','1802','1007','1959',117),
	(123,'US','CLT','319','2010-07-05','1430','1920','1032','2000',40),
	(131,'US','CLT','319','2010-07-05','1777','1916','1032','2000',44),
	(134,'US','CLT','319','2010-07-05','1855','1911','1032','2000',49),
	(124,'US','CLT','319','2010-07-05','1463','1911','1032','2000',49),
	(122,'US','CLT','319','2010-07-05','1400','1908','1032','2000',52),
	(133,'US','CLT','319','2010-07-05','1794','1906','1032','2000',54),
	(130,'US','CLT','319','2010-07-05','1773','1855','1032','2000',65),
	(117,'US','CLT','319','2010-07-05','1047','1835','1032','2000',85),
	(118,'US','CLT','319','2010-07-05','1060','1822','1032','2000',98),
	(128,'US','CLT','319','2010-07-05','1669','1802','1032','2000',118),
	(149,'US','CLT','319','2010-07-05','1430','1920','1047','1948',28),
	(157,'US','CLT','319','2010-07-05','1777','1916','1047','1948',32),
	(160,'US','CLT','319','2010-07-05','1855','1911','1047','1948',37),
	(150,'US','CLT','319','2010-07-05','1463','1911','1047','1948',37),
	(148,'US','CLT','319','2010-07-05','1400','1908','1047','1948',40),
	(159,'US','CLT','319','2010-07-05','1794','1906','1047','1948',42),
	(156,'US','CLT','319','2010-07-05','1773','1855','1047','1948',53),
	(143,'US','CLT','319','2010-07-05','1060','1822','1047','1948',86),
	(154,'US','CLT','319','2010-07-05','1669','1802','1047','1948',106),
	(163,'US','CLT','319','2010-07-05','1899','1709','1047','1948',159),
	(183,'US','CLT','319','2010-07-05','1983','0705','1050','0800',55),
	(182,'US','CLT','319','2010-07-05','1945','0648','1050','0800',72),
	(249,'US','CLT','319','2010-07-05','966 ','1047','1059','1135',48),
	(248,'US','CLT','319','2010-07-05','945 ','1036','1059','1135',59),
	(242,'US','CLT','319','2010-07-05','1792','1032','1059','1135',63),
	(244,'US','CLT','319','2010-07-05','1851','1030','1059','1135',65),
	(243,'US','CLT','319','2010-07-05','1821','1019','1059','1135',76),
	(247,'US','CLT','319','2010-07-05','875 ','1013','1059','1135',82),
	(250,'US','CLT','319','2010-07-05','972 ','0823','1059','1135',192),
	(241,'US','CLT','319','2010-07-05','1141','0820','1059','1135',195),
	(246,'US','CLT','319','2010-07-05','1983','0705','1059','1135',270),
	(245,'US','CLT','319','2010-07-05','1945','0648','1059','1135',287)
DECLARE @BestMatches AS TABLE(
	[UniqueID] [bigint] NOT NULL PRIMARY KEY CLUSTERED,
	[Car] [char](2) NOT NULL,
	[City] [char](3) NOT NULL,
	[EType] [char](3) NOT NULL,
	[IDDate] [date] NOT NULL,
	[InID] [char](4) NOT NULL UNIQUE WITH (IGNORE_DUP_KEY = ON),
	[InTime] [char](4) NOT NULL,
	[OutID] [char](4) NOT NULL UNIQUE WITH (IGNORE_DUP_KEY = ON),
	[OutTime] [char](4) NOT NULL,
	[TotalTime] [int] NOT NULL
)

INSERT INTO @BestMatches
SELECT UniqueID,Car,City,EType,IDDate,InID,InTime,OutID,OutTime,TotalTime
FROM @PossibleMatches 
ORDER BY Car,City,EType,IDDate,OutID,TotalTime

SELECT * FROM @BestMatches

Expected output:

(7,'US','CLT','319','2010-07-05','1430','1920','1007','1959',39)
(131,'US','CLT','319','2010-07-05','1777','1916','1032','2000',44),
(160,'US','CLT','319','2010-07-05','1855','1911','1047','1948',37),
(183,'US','CLT','319','2010-07-05','1983','0705','1050','0800',55),
(249,'US','CLT','319','2010-07-05','966 ','1047','1059','1135',48),

Actual output:

(7,'US','CLT','319','2010-07-05','1430','1920','1007','1959',39)
(183,'US','CLT','319','2010-07-05','1983','0705','1050','0800',55),
(249,'US','CLT','319','2010-07-05','966 ','1047','1059','1135',48),

Jared

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources