Mastering SQL INSERT INTO Statements in SQL Server

The INSERT INTO statement in SQL Server is a fundamental command for adding new records to your database tables. Whether you’re a database administrator, a developer, or just learning SQL, understanding how to effectively use INSERT INTO is crucial. This guide will walk you through the syntax, provide practical examples, and demonstrate best practices for using INSERT INTO in SQL Server.

Understanding the Basic Syntax of INSERT INTO

There are two primary ways to write the INSERT INTO statement in SQL Server, offering flexibility based on your needs.

1. Specifying Column Names and Values

This method is explicit and recommended for clarity, especially when not inserting values into every column. You specify the columns you are inserting into, followed by the corresponding values in the VALUES clause.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Here, table_name is the name of the table you are inserting data into, and column1, column2, column3, etc., are the names of the columns you are populating. value1, value2, value3, etc., are the values you want to insert into those respective columns. The order of values must match the order of columns specified.

2. Inserting Values into All Columns

If you are inserting values into every column of the table, and you know the column order, you can omit the column names in the INSERT INTO statement. However, it’s vital to ensure the values are listed in the exact order of the columns as defined in your table schema.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

While this syntax is shorter, it can be less readable and more prone to errors if the table structure changes. Specifying column names (method 1) is generally considered best practice for maintainability and clarity.

Practical Examples of INSERT INTO in SQL Server

Let’s illustrate the use of INSERT INTO with examples based on a sample Customers table. Imagine a table with the following structure:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland

This table stores customer information, including an auto-incrementing CustomerID.

Inserting a Single Record into SQL Server

To add a new customer named ‘Cardinal’ to the Customers table, we can use the following SQL INSERT INTO statement:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

This statement explicitly lists the columns being populated and provides the corresponding values. After executing this, the Customers table will be updated with the new record:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	Tom B. Erichsen	Skagen 21	Stavanger	4006	Norway

Notice that we did not specify a value for CustomerID. Because CustomerID is likely configured as an auto-incrementing identity column in SQL Server, the database automatically generates a unique, sequential number for the new record.

Inserting Data into Specific Columns

You might not always have data for every column when inserting a new record. SQL Server allows you to insert data into only specified columns. For instance, if we only have the CustomerName, City, and Country for a new customer, we can use:

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

In this case, only the CustomerName, City, and Country columns will receive the provided values. Other columns, like ContactName and Address, will be set to their default values (often NULL if no default is defined).

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	null	null	Stavanger	null	Norway

Inserting Multiple Records in SQL Server

To improve efficiency, SQL Server allows inserting multiple rows with a single INSERT INTO statement. Separate each set of values with a comma within the VALUES clause:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
       ('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
       ('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

This statement inserts three new customer records in one go. This approach is generally more performant than executing multiple individual INSERT INTO statements, especially when inserting a large number of records.

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	Tom B. Erichsen	Skagen 21	Stavanger	4006	Norway
93	Greasy Burger	Per Olsen	Gateveien 15	Sandnes	4306	Norway
94	Tasty Tee	Finn Egan	Streetroad 19B	Liverpool	L1 0AA	UK

Conclusion

The INSERT INTO statement is a powerful tool for populating your SQL Server databases. By understanding the different syntax options and utilizing best practices like specifying column names and batch inserting, you can efficiently manage and grow your data. Mastering INSERT INTO is a fundamental step in becoming proficient in SQL Server database management.