SQL Server Table Management _ details of the operation of the data additions and deletions (case code)

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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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:

City

CustomerID	CustomerName	ContactName	Address		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)