Lesson 17. Creating Data

Time to start learning CRUDOPS.

Cr – Create

U – Update

D – Delete

Usually data is machine generated or generated by a user on the other end of an interface. However, sometimes you have to add data by hand.*

The important part of adding data is to make sure you do not violate the data type of the column you are working with. Also, there may be other constraints. If the table has been well implemented, you will be prevented from doing anything bad.

You create data with the SQL INSERT statement. There is more than one way to do this. I am going to teach you my preferred way.

*Obviously if you are an app developer you have to learn how to create, update, and delete data from an application. This tutorial is written from the perspective of business analytics.

Examples

Inserting One Record

For simplicity, I'm going to create a temporary table for this example. Temporary tables are discussed in the advanced section.

USE demo

DECLARE @Person AS TABLE (ID INT, FName NVARCHAR(100), LNAME NVARCHAR(100))

INSERT INTO @Person(ID, FName, LNAME)
SELECT 1,'Bob','Wakefield'

SELECT *
FROM @Person

Inserting More Than One Record

USE demo

DECLARE @Person AS TABLE (ID INT, FName NVARCHAR(100), LNAME NVARCHAR(100))

INSERT INTO @Person(ID, FName, LNAME)
SELECT 1,'Bob', 'Wakefield'
UNION
SELECT 2,'Vanna','White'
UNION
SELECT 3,'Pat','Sajak'


SELECT *
FROM @Person