CRUD (Create, Read, Update, Delete) Part 1 – Create

Here are several examples for insert process (part of CRUD) in SQL Server. I’m using SQL Server 2005 and hopefully it can be used for SQL Server newer version. To try this tutorial, you only need tempdb from SQL Server which is must be available after installing it.

[code language=”sql”]
— CHECK WHETHER TABLE IS AVAILABLE
IF OBJECT_ID(‘TEMPDB.DBO.#SAMPLE_TABLE’) IS NOT NULL
DROP TABLE #SAMPLE_TABLE

— CREATE TABLE
CREATE TABLE #sample_table (
ID INT IDENTITY PRIMARY KEY,
COL1 VARCHAR(20),
COL2 VARCHAR(20),
COL3 VARCHAR(20)
)

— BASIC INSERT
INSERT INTO #sample_table
(COL1, COL2, COL3)
VALUES
(‘VAL11’, ‘VAL21’, ‘VAL31’);

— MULTIPLE INSERT (SQL SERVER 2005)
INSERT INTO #sample_table
(COL1, COL2, COL3)
SELECT ‘VAL12’, ‘VAL22’, ‘VAL32’ UNION ALL
SELECT ‘VAL13’, ‘VAL23’, ‘VAL33’ UNION ALL
SELECT ‘VAL14’, ‘VAL24’, ‘VAL34’;

— MULTIPLE INSERT USING CTE (COMMON TABLE EXPRESSION)
WITH USING_CTE (COL1, COL2, COL3)
AS
(
SELECT ‘VAL12’, ‘VAL22’, ‘VAL32’ UNION ALL
SELECT ‘VAL13’, ‘VAL23’, ‘VAL33’ UNION ALL
SELECT ‘VAL14’, ‘VAL24’, ‘VAL34’
)

INSERT INTO #sample_table
(COL1, COL2, COL3)
SELECT ‘CTE_’ + COL1, ‘CTE_’ + COL2, ‘CTE_’ + COL3 FROM USING_CTE

— MULTIPLE INSERT (SQL SERVER 2008 OR MORE)
INSERT INTO #sample_table
(COL1, COL2, COL3)
VALUES
(‘VAL12’, ‘VAL22’, ‘VAL32’), (‘VAL13’, ‘VAL23’, ‘VAL33’), (‘VAL14’, ‘VAL24’, ‘VAL34’);

— INSERT FROM TABLE
INSERT INTO #sample_table
(COL1, COL2, COL3)
SELECT ‘#’ + COL1, ‘#’ + COL2, ‘#’ + COL3 FROM #sample_table

— TO CHECK
SELECT * FROM #sample_table
[/code]

Marifnst, 20130427