Create
Creating table in SQL is very simple. The syntax is in this following format
1 2 3 4 |
CREATE TABLE #NAME ( COLUMN_NAME DATA_TYPE ) |
1 2 3 4 5 6 7 |
CREATE TABLE #TEMP ( ID INT PRIMARY KEY IDENTITY(1,1), FIRST_NAME VARCHAR(50) NOT NULL, MIDDLE_NAME VARCHAR(50), LAST_NAME VARCHAR(50) NOT NULL ) |
The command will create a temporary table called TEMP with four columns. PRIMARY KEY IDENTITY(1,1) will make ID as a primary key and will start at 1 and automatically increase by 1. Without IDENTITY(1,1), we need to manually put the entry in ID column.
Insert
The syntax is
1 2 3 4 |
INSERT INTO TABLE_NAME VALUES (.....), (.....); |
1 2 3 |
INSERT INTO #TEMP VALUES ('A','B','C') |
If we need to add a new entry, we just need to repeat the same process.
1 2 3 4 |
INSERT INTO #TEMP VALUES ('D', NULL, 'E'), ('F', 'G', 'H') |
1 |
SELECT * FROM #TEMP |
1 2 3 4 |
ID FIRST_NAME MIDDLE_NAME LAST_NAME 1 A B C 2 D NULL E 3 F G H |
Update
Let’s say we want to change some entry.
1 2 3 |
UPDATE #TEMP SET MIDDLE_NAME = 'XXX' WHERE FIRST_NAME = 'F' |
1 2 3 4 |
ID FIRST_NAME MIDDLE_NAME LAST_NAME 1 A B C 2 D NULL E 3 F XXX H |
Or we can also change the entire column
1 2 |
UPDATE #TEMP SET LAST_NAME = 'SET' |
1 2 3 4 |
ID FIRST_NAME MIDDLE_NAME LAST_NAME 1 A B SET 2 D NULL SET 3 F XXX SET |
Delete
We certainly can delete if we can create.
1 2 |
DELETE #TEMP WHERE MIDDLE_NAME = 'XXX' |
1 2 3 |
ID FIRST_NAME MIDDLE_NAME LAST_NAME 1 A B SET 2 D NULL SET |
But if we choose to delete all records without deleting a table
1 |
DELETE #TEMP |
Let’s add a new value and see the result
1 2 3 4 5 |
INSERT INTO #TEMP VALUES ('A','B','C') SELECT * FROM #TEMP |
1 2 |
ID FIRST_NAME MIDDLE_NAME LAST_NAME 4 A B C |
ID is 4.
Drop
To get rid of the entire table is DROP .
1 |
DROP TABLE #TEMP |
And if we were to select anything from that table
1 |
SELECT * FROM #TEMP |
1 |
Invalid object name '#TEMP'. |