SQL Server Table Management _ Detailed information on the operation of data deletion and modification (case code)-DML1. SQL INSERT INTO statement (insert in table)
The INSERT INTO statement is used to insert a new record into the table.
SQL INSERT into Syntax
The INSERT into statement can be written in two ways.
The first form does not specify the name of the column for which you want to insert data, only the value you want to insert:
INSERT into table_name (column1, Column2, Column3, ...) VALUES (Value1, value2, Value3, ...);
If you want to add values for all the columns in the table, you do not need to specify the column names in the SQL query. However, make sure that the values are in the same order as the columns in the table. The INSERT into syntax is as follows:
INSERT into table_name VALUES (Value1, value2, Value3, ...);
Demo Database
The following is the data in the "Customers" table:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover |
Karl Jablonski |
305-14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
Ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
INSERT into Instance code
Suppose we want to insert a new row in the "Customers" table.
We can use the following SQL statement:
Example
insert into Customers (CustomerName, ContactName, Address, City, PostalCode, country) values ( cardinal ' , ' tom B. Erichsen , " ' , " stavanger , " 4006 , ' norway ' );
Now, the data selected from the "Customers" table looks like this:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover |
Karl Jablonski |
305-14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
Ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
92 |
Cardinal |
Tom B. Erichsen |
Skagen 21 |
Stavanger |
4006 |
Norway |
Insert data only in the specified column
We can also insert data in only the specified columns.
The following SQL statement inserts a new row, but only inserts data in the CustomerName, city, and Countryn columns (the CustomerID field is automatically updated):
Instance
INSERT into Customers (CustomerName, City, country) VALUES ('Cardinal'Stavanger'Norway ');
Now, the data selected from the "Customers" table looks like this:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover |
Karl Jablonski |
305-14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
90 |
Wilman Kala |
Matti Karttunen |
Keskuskatu 45 |
Helsinki |
21240 |
Finland |
91 |
Wolski |
Zbyszek |
Ul. Filtrowa 68 |
Walla |
01-012 |
Poland |
92 |
Cardinal |
Null |
Null |
Stavanger |
Null |
Norway |
2. SQL DELETE statement (delete records from table)
The DELETE statement is used to delete existing records in the table.
SQL DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
DELETE from table_name WHERE condition;
The following is the data in the "Customers" table:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. De la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds Snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Across |
S-958 22 |
Sweden |
SQL DELETE Instance Code
Suppose we want to remove the customer "Alfreds Futterkiste" from the "Customers" table.
We use the following SQL statement:
Example
DELETE from Customers WHERE CustomerName='Alfreds futterkiste';
Now, the "Customers" table looks like this:
CustomerID |
CustomerName |
Contactnam E |
Address |
city |
PostalCode |
country |
2 |
ana Trujillo emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno taquería |
Ant Onio Moreno |
mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
berglunds snabbköp |
Christina berglund |
berguvsvägen 8 |
luleå |
S-958 |
Sweden |
Delete all data
You can delete all rows in a table without having to delete the table. This means that the structure, properties, and indexes of the table will remain the same:
DELETE from table_name;
Or
DELETE * from table_name;
Note: in the absence of a backup, delete the record with extra care! Because you deleted it can't be repeated!
3. SQL UPDATE statement (records in the Update table) modified
The UPDATE statement is used for updating existing records in the table.
SQL UPDATE Statement
The UPDATE statement is used for updating records that already exist in the table.
SQL UPDATE Syntax
UPDATE table_name SET = = value2, ... WHERE condition;
Demo Database
The following is the data in the "Customers" table:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. De la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4 |
Around the Horn |
Thomas Hardy |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds Snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Across |
S-958 22 |
Sweden |
SQL UPDATE Instance
The following SQL statement updated "CustomerName" and "City" for the first customer (CustomerID = 1):
Instance
UPDATE Customers SET = ' Alfred Schmidt ', city='Frankfurt'WHERE=1;
Now, the data selected from the "Customers" table looks like this:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Alfred Schmidt |
Obere Str. 57 |
Frankfurt |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. De la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds Snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Across |
S-958 22 |
Sweden |
update multiple records
The WHERE clause determines the number of records that will be updated.
The following SQL statement updates the contact name for all records in the country/region "Mexico" to "Juan":
UPDATE Customers SET ContactName='Juan'WHERE Country='Mexico ';
The selections in the "Customers" table now look like this:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Alfred Schmidt |
Obere Str. 57 |
Frankfurt |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Juan |
Avda. De la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Juan |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Thomas Hardy |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds Snabbköp |
Christina Berglund |
Berguvsvägen 8 |
Across |
S-958 22 |
Sweden |
Update Warning!
Be careful when updating records. If you omit the WHERE clause, all records will be updated!
UPDATE Customers SET ContactName='Juan';
The "Customers" table will resemble the following:
CustomerID |
CustomerName |
ContactName |
Address |
| City
PostalCode |
Country |
1
|
Alfreds Futterkiste |
Juan |
Obere Str. 57 |
Frankfurt |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Juan |
Avda. De la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Juan |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
4
|
Around the Horn |
Juan |
Hanover Sq. |
London |
WA1 1DP |
UK |
5 |
Berglunds Snabbköp |
Juan |
Berguvsvägen 8 |
Across |
S-958 22 |
Sweden
|
About the view is more complex, after the details of it!!!
SQL Server Table Management _ details of the operation of the data additions and deletions (case code)