Basic Except, Sql Server

Basic Except

-- ///////////SCHEMA///////////////////////////////////////////////////
CREATE TABLE Animals
    ([animal] varchar(255), [legs] int, [colour] varchar(255))
;

CREATE TABLE ObjectPermissions
    ([animal] varchar(255), [permission] varchar(255), [record] int, [test1] varchar(255), [test2] varchar(255), [test3] varchar(255) )
;

INSERT INTO Animals
    ([animal], [legs], [colour])
VALUES
    ('cat', 4, 'red'),
    ('bird', 2, 'yellow'),
    ('dog', 4, 'red'),
    ('deer', 4, 'red'),
    ('fish', 0, 'blue'),
    ('dingo', 4, 'yellow')
;

select * from animals
-- ////////////////////////////////////////////////////////////////////

DECLARE @temptable table (
        animal VARCHAR(max),
        legs VARCHAR(max),
        colour VARCHAR(max)
)

INSERT INTO @temptable (animal, legs, colour) VALUES ('fish' , 0, 'blue')
INSERT INTO @temptable (animal, legs, colour) VALUES ('dog', 4, 'red')

select * into #temp from @temptable

select animal, legs, colour
from Animals
except 
select animal, legs, colour
from #temp

drop table #temp;


run \| edit \| history \| help	0

Please log in to post a comment.