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. Should 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. The values ​​in the 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 is in the same order as the columns of the table, to have a value in each column, or explicitly list specifies that the column that stores each one of the incoming values. The maximum number of rows to be inserted into 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 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, you can not load the row. The database engine automatically provides a value for the column if it:

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

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 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. Insert 5 rows in the table Departments

 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 sp.SalesPersonID e.EmployeeID = Person.Contact AS c INNER JOIN ON and . WHERE ContactID = c.ContactID LIKE '2% e.EmployeeID 'ORDER BY e.EmployeeID, c.LastName