SQL with Manoj

The following exercise shows multiple ways to INSERT records in a table, as the post title says.

USE [tempdb]
GO

CREATE TABLE sometable(a INT, b VARCHAR(20), c INT)
GO

-- Method #1 - Simple INSERT statement
INSERT sometable (a, b, c)
VALUES(1, 'New York', 123)
GO

DROP TABLE sometable

-- Method #2 - CREATE the table and INSERT records. This is minimally logged operation and faster than explicitly creating table and inserting records.
SELECT 1 a, 'New York' b, 334 c
INTO sometable
UNION
SELECT 2, 'London', 823
UNION
SELECT 3, 'Paris', 1124
UNION
SELECT 4, 'Munich', 2080
GO

-- Method #3
INSERT sometable (a, b, c)
 EXEC('SELECT 5, ''New York'', 234
  SELECT 6, ''London'', 923
  SELECT 7, ''Paris'', 1024
  SELECT 8, ''Munich'', 1980')
GO

-- Method #4
INSERT sometable (a, b, c)
 SELECT 9, 'New York', 334 UNION
 SELECT 10, 'London', 823 UNION
 SELECT 11, 'Paris', 1124 UNION
 SELECT 12, 'Munich', 2080
GO

-- Method #5 - More options in SQL Server 2008, by using the VALUES() constructor
INSERT sometable (a, b, c)
VALUES	(13, 'New York', 334),
		(14, 'London', 823),
		(15, 'Paris', 1124),
		(16, 'Munich', 2080))
GO

-- Method #6 - Yes you can also use SQL statements at column level inside the VALUES constructor
INSERT sometable (a, b, c)
VALUES	(18, 'New York', 334),
		(19, 'London', 823),
		((SELECT MAX(a)+1 FROM sometable), (SELECT b FROM sometable WHERE a=15), SELECT SUM(c) FROM sometable),
		(20, 'Munich', 2080))
GO

-- Now check the resultset
SELECT * FROM sometable

-- Final Cleanup
DROP TABLE sometable

More on VALUES constructor on MS BOL: http://technet.microsoft.com/en-us/library/dd776382.aspx