Insert, Update, Delete And Retrieved Data Using SQL Server | SQL Server Homework Help



In this blog we will learn some important SQL Classes to update, insert, delete and retrieved data from database.


Table of content

  • INSERTING Data

  • Inserting: Inserting single row

  • INSERTING Multiple Rows

  • INSERT Data Using INTO

  • INSERTING Retrieved Data

  • INSERT Data Using WHERE Clause

  • NSERT Data Using WHERE Clause


INSERTING Data

In SQL, there are basically two ways to INSERT data INTO a table: One is to INSERT it one row at a time, the other is to INSERT multiple rows at a time. Let's first look at how we may INSERT data one row at a time:


Use CreateSQLClass.txt file for database


Sample Basic Syntax

INSERT INTO tablename (column,column,column,column,column,column,column,column) VALUES( 'VALUE', 'VALUE', 'VALUE', 'VALUE', NULL, NULL ) 

Inserting

INSERTING Single Row

The syntax for INSERT data INTO the customers table one row at a time is as follows. Note: Use the SQLClass database


Write Exercise Code:

INSERT INTO Customers 
(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES( '100010', 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', NULL, NULL, NULL
)

Result

(1 row affected)

Total execution time: 00:00:00.008


Now I can check to confirm the data has been inserted by

SELECT * from Customers









The previous syntax for INSERTING data will work but it is not the recommend way. You should avoid INSERTING data this way. This method depends on the columns being the order you specified. Also, when you type in the values make sure you use a single quote.


The following is a better way to INSERT new single record. This way the columns are specified:


Write Exercise Code

INSERT INTO customers 
	( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) 

VALUES 
	( '100011', 'Elmer Fudd', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', 'Joe 	Smith', 'elmer@fudd.com' 
		)

Result

(1 row affected)

Total execution time: 00:00:00.008


Now I can check to confirm the data has been inserted by

SELECT * from Customers










INSERTING Multiple Rows

Because the INSERT statement only allows you to INSERT one record at a time you need to statements to enter multiple records. You could leave same data or change before each executing.


The syntax for INSERTING multiple rows of data INTO the customers table is as follows:


Write Exercise Code:

INSERT INTO customers 
( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) 

VALUES ( '100012', 'Pep E. Lapew', '100 Main Street', 'Los Angeles', 'CA', '91108','USA', 'Pep E. Lapew', 'plapew@plapew.org' )
INSERT INTO customers 
( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) 

VALUES ( '100013', 'M. Martin', '42 Galaxy Way', 'New York','NY', '11213','USA', 'M. Martin', 'mmartin@mmartin.org' ) 

Result

(1 row affected)

(1 row affected)

Total execution time: 00:00:00.010


INSERT Data Using INTO

The INSERT statement generally is used to add one row at a time. Another way to add data is using the INTO in a SELECT statement. This allows you to INSERT data from a single table or multiple tables using table joins. You can think of the SELECT INTO as being an export operation.


The INTO statement allows you to copy the data from one table into a new table. The keyword INTO actually creates the new table.


The syntax for using the INTO in a SELECT statement is as follows: Write Exercise Code:



SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country,
cust_contact, cust_email 
INTO customers2 
From Customers; 

-- Now let’s insert a row to the new customers2 table 
INSERT INTO customers2 ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) 
VALUES ( '100014', 'J. Verne', '12 Timetravel Way', 'New York','NY', '11213','USA', 'J. Verne', 'jverne@jverne.org' )


Note: When using this syntax, it does not re-create the Primary Key. On the new table Customers2 add Primary key.


To do this, use the following syntax:

ALTER TABLE customers2 
ADD CONSTRAINT pk_customers2 PRIMARY KEY (cust_id);

Results

Commands completed successfully.

Total execution time: 00:00:00.011



INSERTING Retrieved Data

The INSERT statement generally is used to add one row at a time. Another way to add data is using the result set from a SELECT statement; also known as the INSERT SELECT. It is made up of both the INSERT and the SELECT statement. You can think of the INSERT SELECT as being an import operation.


If you wanted to add customers from another table to the customers table all at one time you would use the INSERT SELECT statement.


Note: This code will copy duplicate records into your Customers table.


The syntax for INSERTING a result set from a SELECT statement is as follows:


Write Exercise Code

INSERT INTO customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
 
Select cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email 
From Customers2 
WHERE cust_id=100014

INSERT Data Using WHERE Clause

The INSERT statement generally is used to add one row at a time. You could also use the WHERE clause to filter specific rows. This method allows you to limit what data you are INSERTING.


Note: Check the cust_id in Customer2 table to make sure customer number '111111' is there. Otherwise you will get an error. If you don't have customer number '111111' go ahead and add it.


The following code will INSERT the new single record.


Write Exercise Code

INSERT INTO customers2 
( cust_id, cust_name, cust_addres s, cust_city, cust_state, cust_zip, cust_country , cust_contact , cust_email ) 

VALUES ( '111111' , 'Elmer Fudd', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', 'Joe Smith', 'elmer@fudd.co m' ) 


The syntax for using the WHERE clause in a SELECT statement is as follows:

Write Exercise Code:


INSERT INTO customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) 

SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email 
FROM Customers2 
WHERE cust_id = '111111' 




Updating and Deleting Data

Once there's data in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The UPDATE statement always begins with the name of the table being UPDATED.


The SET command is used to assign new VALUE to a column. The UPDATE statement ends with a WHERE clause. Without a WHERE clause, the UPDATE statement will UPDATE all rows in the table.


The syntax to UPDATE a single column is as follows


Write Exercise Code

UPDATE customers 
SET cust_email = 'elmer@fudd.com' 
WHERE cust_id = '100011'

Result

(1 row affected)

Total execution time: 00:00:00.005


Note: just make sure there is a cust_id number “100011.” If not use another cust_id that is in your dataset.



UPDATING Multiple Columns

To UPDATE multiple columns we use a slightly different syntax. To UPDATE multiple columns you use a single SET command and separate the column names with a comma. No comma after the last column. For example:


Write Exercise Code

UPDATE customers 
SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' 
WHERE cust_id = '100011' 


DELETE Column VALUE

To delete a columns VALUE, we set it to NULL.

You do this as follows:


Write Exercise Code

UPDATE customers 
SET cust_city = NULL 
WHERE cust_id = '111111'


DELETING Single Row

Sometimes we may wish to get rid of records from a table. To do so, we can use the DELETE FROM command. Using the WHERE clause, we can specify which row to delete.


To do this use, the following syntax:


Write Exercise Code

DELETE FROM customers 
WHERE cust_id = '100012'

DELETING ALL Rows

Sometimes we may wish to get rid of records from a table. To do so, we can use the DELETE FROM command.


To DELETE all rows in a table we don't use the WHERE clause. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. In order not to delete the records in the Customer table lets use the Customers2 table.


To do this, use the following syntax:


Write Exercise Code

DELETE FROM customers2 

Drop tables:

Drop Table removes all the data and the entire definition of the table, including any primary keys, indexes, foreign keys, and any link to the table.


Write Exercise Code

Drop Table Customer2;

Truncate ALL Rows

TRUNCATE TABLE removes all rows from a table just like the Delete from command. TRUNCATE TABLE is faster and uses fewer system resources because it does not log every delete transaction in the log file. .

To do this, use the following syntax:

First lets add data back into the Customer2 table. Execute this code


Write Exercise Code

Drop Table Customer2; 
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email 
INTO Customers2 
From Customers; 
 

Then let’s execute this code to truncate the Customers2 table and then execute the select statement that follows to see that all records were deleted.


Write Exercise Code

Truncate Table customers2; 
Select * from customers2;


9 views0 comments