You Can Use the Sql Create Table Command to Insert Rows Into a Table.ã¢â‚¬â€¹
Summary: in this tutorial, you will learn how to use the INSERT argument to add a new row to a tabular array.
Introduction to SQL Server INSERT statement
To add together one or more rows into a tabular array, you utilize the INSERT statement. The post-obit illustrates the near bones course of the INSERT argument:
INSERT INTO table_name (column_list) VALUES (value_list);
Lawmaking language: SQL (Structured Query Language) ( sql ) Permit'southward examine this syntax in more item.
Start, you specify the name of the table which you want to insert. Typically, you reference the tabular array name by the schema proper name due east.g., production.products where production is the schema proper name and products is the table name.
Second, yous specify a list of one or more columns in which you lot desire to insert information. You must enclose the column list in parentheses and separate the columns past commas.
If a column of a tabular array does not appear in the column list, SQL Server must exist able to provide a value for insertion or the row cannot be inserted.
SQL Server automatically uses the post-obit value for the cavalcade that is available in the table but does non appear in the cavalcade list of the INSERT statement:
- The next incremental value if the column has an
IDENTITYproperty. - The default value if the column has a default value specified.
- The electric current timestamp value if the data type of the column is a timestamp data blazon.
- The
Aughtif the column is nullable. - The calculated value if the column is a computed column.
Third, you lot provide a list of values to be inserted in the VALUES clause. Each cavalcade in the cavalcade list must have a corresponding value in the value list. Also, you must enclose the value list in parentheses.
SQL Server INSERT argument examples
Permit's create a new table named promotions for the sit-in:
CREATE TABLE sales.promotions ( promotion_id INT PRIMARY KEY IDENTITY (i, ane), promotion_name VARCHAR (255) NOT NULL, discount NUMERIC (iii, 2) DEFAULT 0, start_date DATE Not NULL, expired_date Engagement Non NULL );
Code language: SQL (Structured Query Language) ( sql ) In this statement, we created a new table named promotions in the sales schema. The promotions table has 5 columns including promotion identification number, name, discount, start date and expired engagement.
The promotion identification number is an identity column so its value is automatically populated by the SQL Server when you lot add a new row to the tabular array.
1) Bones INSERT example
The following argument inserts a new row into the promotions table:
INSERT INTO sales.promotions ( promotion_name, discount, start_date, expired_date ) VALUES ( '2018 Summer Promotion', 0.15, '20180601', '20180901' );
Lawmaking language: SQL (Structured Query Language) ( sql ) In this instance, we specified values for 4 columns in the promotions table. We did not specify a value for the promotion_id column considering SQL Server provides the value for this cavalcade automatically.
If the INSERT argument executes successfully, yous will get the number of rows inserted. In this case, SQL Server issued the following message:
Code language: SQL (Structured Query Language) ( sql )
(1 row afflicted)
To verify the insert operation, you employ the following query:
SELECT * FROM sales.promotions;
Code linguistic communication: SQL (Structured Query Language) ( sql ) Here is the issue as y'all expected.
2) Insert and render inserted values
To capture the inserted values, you utilise the OUTPUT clause. For example, the following statement inserts a new row into the promotions table and returns the inserted value of the promotion_id cavalcade:
INSERT INTO sales.promotions ( promotion_name, disbelieve, start_date, expired_date ) OUTPUT inserted.promotion_id VALUES ( '2018 Fall Promotion', 0.xv, '20181001', '20181101' );
Code language: SQL (Structured Query Language) ( sql ) 
To capture inserted values from multiple columns, you specify the columns in the output as shown in the following statement:
INSERT INTO sales.promotions ( promotion_name, discount, start_date, expired_date ) OUTPUT inserted.promotion_id, inserted.promotion_name, inserted.discount, inserted.start_date, inserted.expired_date VALUES ( '2018 Winter Promotion', 0.2, '20181201', '20190101' );
Lawmaking language: SQL (Structured Query Language) ( sql ) The following is the output:
3) Insert explicit values into the identity column
Typically, you don't specify a value for the identity column considering SQL Server will provide the value automatically.
Even so, in some situations, you may want to insert a value into the identity column such every bit data migration.
See the following INSERT argument:
INSERT INTO sales.promotions ( promotion_id, promotion_name, disbelieve, start_date, expired_date ) OUTPUT inserted.promotion_id VALUES ( four, '2019 Spring Promotion', 0.25, '20190201', '20190301' );
Code language: SQL (Structured Query Language) ( sql ) SQL Server issued the following mistake:
Code language: Shell Session ( shell )
Cannot insert explicit value for identity column in table 'promotions' when IDENTITY_INSERT is set to OFF.
To insert explicit value for the identity column, you lot must execute the post-obit statement first:
SET IDENTITY_INSERT table_name ON;
Code linguistic communication: SQL (Structured Query Language) ( sql ) To switch the identity insert off, you use the like argument:
Set up IDENTITY_INSERT table_name OFF;
Code language: SQL (Structured Query Language) ( sql ) Let'southward execute the following statements to insert a value for the identity column in the promotions table:
SET IDENTITY_INSERT sales.promotions ON; INSERT INTO sales.promotions ( promotion_id, promotion_name, disbelieve, start_date, expired_date ) VALUES ( 4, '2019 Leap Promotion', 0.25, '20190201', '20190301' ); SET IDENTITY_INSERT sales.promotions OFF;
Lawmaking language: SQL (Structured Query Language) ( sql ) In this example, beginning, we switched the identity insert on, then inserted a row with an explicit value for the identity column, and finally switched the identity insert off.
The post-obit shows the data of the promotions table after the insertion:
SELECT * FROM sales.promotions;
Lawmaking language: SQL (Structured Query Language) ( sql ) 
In this tutorial, you lot have learned how to employ the SQL Server INSERT statement to add a new row to a table.
Source: https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert/
0 Response to "You Can Use the Sql Create Table Command to Insert Rows Into a Table.ã¢â‚¬â€¹"
Post a Comment