SQL - Create table

Creating temporary table
1. using #

CREATE TABLE #tbl_user(userid int,username varchar(50)) INSERT INTO #tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALLS SELECT 3, 'user name 3' SELECT * FROM #tbl_user DROP TABLE #tbl_user

note
Here im using the # hash sign when creating the temporary table which tell the SQL Server i want to create a local temporary table located in the tempdb. If you are creating this table within one store procedure, this table will only usable within this store procedure and it will automatically dropped when its out of the store procedure scope

2. using @

DECLARE @tbl_user TABLE ( userid int, username varchar(50)) INSERT INTO @tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALL SELECT 3, 'user name 3' SELECT * FROM @tbl_user

note
Here im using the @ sign when creating the temporary table which tell the SQL Server i want to create a local temporary table in the memory. This variable will auto cleared itself once it goes out of it scope

Creating permanent table

CREATE TABLE tbl_user ( userid int, username varchar(50)) INSERT INTO tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALL SELECT 3, 'user name 3' SELECT * FROM tbl_user --DROP TABLE tbl_user

note
Here im trying to create a permanent table in the same database where this query runs. This is the most common where you want to store the data permanently