SQL Insert

INSERT
Definition
Adds a new row or rows to a table or view.

 INSERT [INTO] {object [(lista_columnas)] {VALUES (({DEFAULT | NULL | expression} [, ... n]) [, ... n]) | tabla_derivada | sentencia_ejecutable | DEFAULT VALUES}}

Description of the clauses

• INTO: An optional keyword that can be used between INSERT and the target table.
• Lista_columnas: A list of one or more columns in which data is inserted. Must be enclosed in parentheses and delimited by commas.
• VALUES: Presents a list of data values ​​to be inserted. There must be a data value for each column of the list, if specified, or table. The list of values ​​must be enclosed in parentheses. Values ​​VALUES list must be in the same order as the list of columns. The insertion of more than one row of values ​​requires the VALUES list are in the same order as the columns of the table, to have a value in each column, or list explicitly specify the column in which that stores each one of the incoming values. The maximum number of rows that can be inserted in a single INSERT statement is 1000. To insert more than 1000 rows, create multiple INSERT, or perform a bulk import data using the BCP utility or the BULK INSERT.
• Tabla_derivada: Any valid SELECT statement that returns rows of data to be loaded into the table.
• Sentencia_ejecutable: Is any valid EXECUTE statement that returns data with SELECT statements. It may contain a call to a stored procedure.
• DEFAULT VALUES: It makes the new row contains the default values ​​defined for each column.

Considerations
If a column is not included in the list of columns, the database engine must be able to provide a value based on the definition of the column, otherwise, can not load the row. The database engine automatically provides a value for the column if it:

• Has an IDENTITY property. Use the next incremental identity value.
• It has a default value. Use the default value of the column.
• It has a timestamp data type. It uses the current value of time stamp.
• Nullable. Use a NULL value.
• Is a computed column. We use the calculated value.

Examples
a. Inserts a row in the table UnitMeasure. Because the values ​​for all columns are supplied and included in the same order as the columns of the table, it is not necessary to specify the column names in the list. It is also good practice to declare the list forever.

 Production.UnitMeasure INSERT INTO VALUES ('F2', 'Square Feet', GETDATE ())

b. Same example as in the option, but specifying the column list.

 INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES ('Square Yards', 'Y2', GETDATE ())

c. 5 rows inserted into the Departments table

 Dbo.Departments INSERT INTO VALUES (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Byham'), (3, 'Finance', 'Gill'), (4, 'Purchasing', 'Barber'), (5, 'Manufacturing', 'Brewer')

d. Insert the result of the SELECT statement in the table EmployeeSales

 Dbo.EmployeeSales INSERT SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD FROM HumanResources.Employee AS e INNER JOIN AS sp ON Sales.SalesPerson e.EmployeeID = sp.SalesPersonID Person.Contact AS c INNER JOIN ON and . WHERE ContactID = c.ContactID e.EmployeeID LIKE '2% 'ORDER BY e.EmployeeID, c.LastName