This article on the SQL Insert statement is part of a series on string manipulation functions, operators and techniques. The previous articles are focused on SQL query techniques, all centered around the task of data preparation and data transformation.
The general format is the INSERT INTO SQL statement followed by a table name, then the list of columns, and then the values that you want to use the SQL insert statement to add data into those columns. Inserting is usually a straightforward task. It begins with the simple statement of inserting a single row. Many times, however, it is more efficient to use a set-based approach to create new rows. In the latter part of the article, let’s discuss various techniques for inserting many rows at a time.
The assumption is that you have the following the permission to perform the insert operation on a table
- Insert operation defaults to the members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner.
- Insert with the OPENROWSET BULK option requires a user to be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.
- Download AdventureWorks2014 here