SQL: Create Rerunnable Script to Insert Default Data Into a Table

5/14/2014 2:54:45 AM

Sometimes you need to initialize a database with default data such as lookup data. The following script will insert the data and is rerunnable so it can be run again without insert previously inserted data.

--CONTENT TYPES
INSERT INTO	ContentTypes (Name, ContentTypeId)

SELECT		*
			
FROM		(
			SELECT	'Post' Name, 1 ContentTypeId UNION ALL
			SELECT	'Forum' Name, 2 ContentTypeId UNION ALL
			SELECT	'Thread' Name, 3 ContentTypeId UNION ALL
			SELECT	'Blog' Name, 4 ContentTypeId
			) AS DefaultData
			
WHERE		NOT EXISTS (SELECT TOP 1 * FROM ContentTypes ExistingData WHERE ExistingData.ContentTypeId = DefaultData.ContentTypeId)