Insert Multiples Row Into Single Table

Acheive target with minimal cost is hot topic now a days. SQL Server provides different approaches to acheive targets. Here I can show you these approaches.

USE DBTester
GO

-- Create Table

CREATE TABLE Test
(
    TestId int not null,
    TestValue nvarchar(100) not null
)

-- Beginner Approach I

INSERT INTO Test (TestId, TestValue)
VALUES 1, 'One'

INSERT INTO Test (TestId, TestValue)
VALUES 2, 'Two'

INSERT INTO Test (TestId, TestValue)
VALUES 3, 'Three'

TRUNCATE TABLE Test

-- Beginner Approach II

INSERT INTO Test (TestId, TestValue)
SELECT 1, 'One'

INSERT INTO Test (TestId, TestValue)
SELECT 2, 'Two'

INSERT INTO Test (TestId, TestValue)
SELECT 3, 'Three'

TRUNCATE TABLE Test

-- Intermediate Approach

INSERT INTO Test (TestId, TestValue)
SELECT 1, 'One'
UNION ALL
SELECT 2, 'Two'
UNION ALL
SELECT 3, 'Three'

TRUNCATE TABLE Test

-- Advance Approach (SQL 2008 Feature named as Row Construction)

INSERT INTO Test (TestId, TestValue)
VALUES (1, 'One'), (2, 'Two'), (3, 'Three');

TRUNCATE TABLE Test

DROP TABLE Test