SQL Server Insert Example

Question

I switch between Oracle and SQL Server occasionally, and often forget how to do some of the most trivial tasks in SQL Server. I want to manually insert a row of data into a SQL Server database table using SQL. What is the easiest way to do that?

For example, if I have a USERS table, with the columns of ID (number), FIRST_NAME, and LAST_NAME, what query do I use to insert a row into that table?

Also what syntax do I use if I want to insert multiple rows at a time?

Answer 1

To insert a single row of data:

INSERT INTO USERS VALUES (1, 'Mike', 'Jones'); 

To do an insert on specific columns (as opposed to all of them) you must specify the columns you want to update.

INSERT INTO USERS (FIRST_NAME, LAST_NAME) VALUES ('Stephen', 'Jiang'); 

To insert multiple rows of data in SQL Server 2008 or later:

INSERT INTO USERS VALUES (2, 'Michael', 'Blythe'), (3, 'Linda', 'Mitchell'), (4, 'Jillian', 'Carson'), (5, 'Garrett', 'Vargas'); 

To insert multiple rows of data in earlier versions of SQL Server, use "UNION ALL" like so:

INSERT INTO USERS (FIRST_NAME, LAST_NAME) SELECT 'James', 'Bond' UNION ALL SELECT 'Miss', 'Moneypenny' UNION ALL SELECT 'Raoul', 'Silva' 

Note, the "INTO" keyword is optional in INSERT queries. Source and more advanced querying can be found here.