Let's get started on CTEs with data modifying statements. First, we will review the syntax of the INSERT statement. This statement is used to add data to a table. For example:

INSERT INTO GroceryStore (Id, Name, City)
VALUES (9, N'Small Grocery Store', N'Washington');

The statement above inserts a new row into the GroceryStore table.

The statement starts with INSERT INTO, followed by the table name, and then the names of columns in brackets. Following these is the keyword VALUES, and inside the brackets is the list of values for the new row. Notice that the columns names and the values are separated by commas. The values must be in exactly the same order as the column names.

There is also a shorter version of the INSERT statement where you can set data into all of the columns in the table. Take a look:

INSERT INTO GroceryStore
VALUES (9, N'Small Grocery Store', N'Washington');

In this variation, you omit the list of column names and list only the values. In this case, you must list the values in exactly the same order as the columns appear in the table.