Rookie question about SQL, please master answer

If I have a list of A and B two column
A B
Line 1100200

I want now
Line 2100100
Or
Line 2200200
Can write,

But don't want
Line 2100200
Can write

So how to achieve? Please master to help answer, thank you

Started by Kirk at November 17, 2016 - 9:55 AM

Although not quite understand what you mean, but from the example I think you want two columns cannot be duplicated, and if so, on AB two column and a unique index can give an example, the following you:
CREATE TABLE #t(a INT,b int)
INSERT INTO #t
        ( a, b )
VALUES  ( 100,200
          )

Create unique index: 
CREATE UNIQUE INDEX test ON #t(a,b)

Test: 
INSERT INTO #t
VALUES(100,200)
/*
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#t' with unique index 'test'. The duplicate key value is (100, 200).
The statement has been terminated.

*/

INSERT INTO #t
SELECT 100,100
UNION ALL 
SELECT 200,200

/*
(2 row(s) affected)
*/

Posted by Grover at November 30, 2016 - 10:52 AM

Can, is a relatively simple method, by creating a unique index, to limit cannot insert duplicate data:
if object_id('tb') is not null
   drop table tb
go

CREATE TABLE tb(a INT,b int)

Create unique index
create unique index uni_idx_tb_ab ON tb(a,b)


Insert first data
INSERT INTO tb( a, b )
VALUES  ( 100,200)

Insert second data, no errors
INSERT INTO tb
values(100,100)

Insert third data, no errors
insert into tb 
SELECT 200,200


Insert fourth data, error, fulfill your requirements
INSERT INTO tb
VALUES(100,200)
/*
Message 2601, level 14, state 1, line fourth
Cannot insert duplicate key in a unique index'uni_idx_tb_ab'objects in'dbo.tb'. 
The statement has been terminated. 
*/

Posted by Abigail at December 05, 2016 - 11:40 AM

;with cte as
(
select A from #tb union all select B from #tb
)
INSERT INTO #tb
select *
from (
select a.A,b.A as B
from cte a,cte b
)t
where not exists(select 1 from #tb a where a.A=t.A and a.B=t.B )

select * from #tb
drop table #tb

/*
A	B
100	200  -->Already inserted
100	100
200	100  
200	200
*/

Posted by Ashbur at December 07, 2016 - 11:43 AM

Insert the check, if A=100, B=200 will not insert

Posted by Elsa at December 08, 2016 - 11:54 AM

There are many ways to solve this... All the above solutions. . .

Posted by Matt at December 16, 2016 - 12:27 PM

By trigger.
create table ss(A int,B int)

insert into ss(A,B)
 select 100,200


Create trigger
create trigger tr_ss
 on ss instead of insert
as
begin
 insert into ss(A,B)
  select x.A,x.B 
   from inserted x
   left join ss y on x.A=y.A and x.B=y.B
   where y.A is null and y.B is null
end


Test
insert into ss(A,B)
 select 100,100 union all
 select 200,200 union all
 select 100,200

As a result,
select A,B from ss

/*
A           B
----------- -----------
100         200         --> The original record
100         100         --> Can write
200         200         --> Can write

(3 row(s) affected)
*/

Posted by Brady at December 22, 2016 - 12:44 PM

Method a lot of, can be judged through program end, can also lead to judge by the only cable. The landlord can according to personal preferences to choose the kind of.

Posted by Maureen at January 02, 2017 - 1:33 PM