#Temp Table fill, Sql Server

#Temp Table fill

-- ///////////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
-- ////////////////////////////////////////////////////////////////////

-- If temp table has not been dropped then drop it, this will prevent errors whereby you try to create a temp table that already exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

CREATE table #temp (
        animal VARCHAR(max) COLLATE database_default,
        legs VARCHAR(max) COLLATE database_default,
        colour VARCHAR(max) COLLATE database_default
)

INSERT INTO #temp (animal, legs, colour) VALUES 
('fish' , 0, 'blue'), 
('dog', 4, 'red'),
('Giraffe', 2, 'Orange'),
('Butterfly', 0, 'red')

insert Animals (animal, legs, colour)
select animal, legs, colour
from #temp
except
select animal, legs, colour
from Animals

drop table #temp;

select * from animals


run \| edit \| history \| help	0