Making Data Modifications


As time passes , new data will no doubt need to be inserted, redundant data will need to be deleted, and existing data will need to be updated. Data may be modified with a few simple lines of T-SQL. Data modification is always necessary when working with real-life situations. Think, for example, of an employee changing houses . His address would need to be modified, which could be done using the UPDATE statement.

Data modification is changing, deleting, or inserting data, and this section will teach you just that. Basically, there are three T-SQL statements that enable you to insert, delete, and update data; unsurprisingly, they are INSERT , DELETE , and UPDATE .

Inserting Data into Tables

Although there are many ways to insert data into an existing table, such as using the Enterprise Manager, this section deals with the primary coding method of using the INSERT statement.

The syntax of the INSERT INTO statement can be summarized as follows :

 INSERT [INTO  ] table_or_view  [(column_list)] VALUES data_values 

The statement causes the data values to be inserted as one or more rows into the named table. Column list is a comma-separated list of column names that can be used to indicate the columns for which data is supplied.

Remember, it's always a safe habit to practice modifying data on a test table. For these examples, use the table defined as follows:

 CREATE TABLE TestTable  ( age int NULL,    month varchar(8) NULL,    phone varchar(15) NULL,    gender varchar(7) NULL,    haircolor varchar(14) NULL ) 

Now, to insert the data, enter the query as displayed in Figure 4.6.

Figure 4.6. Insert of data.

graphics/04fig06.jpg

Alternatively, you can specify just the VALUES keyword, which is a more convenient method. Values is required unless you are doing INSERT , SELECT OR INSERT , or EXECUTE . The same results are produced with the following query:

 INSERT INTO TestTable Values(34, 'may', '2802695', 'male', 'black') 

Data inserted must meet the parameters defined by the table structure. This means that NOT NULL columns must have data provided either through input or through the use of column definitions that provide for their own values. A column can obtain its input value through a DEFAULT , IDENTITY , formula, or default object. Data must also meet all rules and constraints that have been defined in the table schema.

Data can be inserted into a table from the resultset of a SELECT query. When this is performed it enables a large amount of data to be extracted from a table or view and stored into another table.

INSERT INTO SELECT

A SELECT statement can be used within the INSERT statement to add values into a table from one or more other tables or views. Using a SELECT subquery is also a mechanism that enables more than one row to be inserted at one time. This type of INSERT statement is often used to insert data into a separate table from some other table or data source. In this manner the data can be copied or just separated off for handling of exceptions or specialty tasks . For example, imagine you would like to copy all your current employees into a customer table. This will enable your employees to make purchases and, of course, allow for an employee discount (not implemented in the Northwind example). The query to perform this operation might look similar to the following:

 INSERT INTO Northwind.dbo.Customers        SELECT EmployeeID, 'Northwind',               FirstName + ' ' + LastName,               'Employee', Address, City, Region,               PostalCode, Country, HomePhone, NULL           FROM Northwind.dbo.Employees 

The SELECT list of the subquery must match the column list of the INSERT statement. If no column list is specified, the SELECT list must match the columns in the table or view being inserted into, as in the example. Note that NULL has been provided for a fax number at the end of the column list, because none is included in the employees table. The INSERT SELECT statement can be used to insert data from any viable source. This includes SQL Server tables, views, and sources outside SQL Server.

Values can be inserted from any number of sources using a variety of options available to an INSERT operation. As you will see next , this is a very flexible operation and virtually any data source can be used.

INSERT EXECUTE

An EXECUTE statement that returns data with SELECT or READTEXT statements can be used to return the insert values to an INSERT operation. Each resultset must be compatible with the columns in the table or in the column list being used by the INSERT . In this manner a stored procedure can be executed and the data returned as input to a table. If an operation returns data with the READTEXT statement, each individual READTEXT statement can return a maximum of 1MB. The execute operation can also be used with extended procedures (not available in previous versions of SQL Server).

Using an operation in this manner enables complex logic to be saved into a stored procedure, and the resulting output can be used for the insertion. For example, imagine you want to get a listing of top salespeople to establish year-end bonuses. You would like to place these records into a separate table. This operation would require four tables be queried on each of three servers and calculations be performed to determine each salesperson's ranking. If you create a procedure called pick_top_sales , the results of this procedure could be used to form the data for your table. The query would look similar to the following:

 INSERT Top_Sales EXECUTE pick_top_sales 

Using this facility, you can get virtually any data from anywhere and feed it to any destination.

SELECT INTO

The SELECT INTO statement can perform a data insertion and create the table for the data in a single operation. The new table is populated with the data provided by a FROM clause. A simple example of its use is as follows:

 SELECT * INTO ObsoleteProducts  FROM Products WHERE Discontinued = 1 

In the example, the SELECT INTO creates a new table ObsoleteProducts with an identical structure as the products table. It then copies all data that meets the WHERE condition into this newly created table. It is possible to combine data from several tables or views into one table, and again a variety of sources can be used.

Deleting Data

Data that is not needed can be deleted using the DELETE statement. The DELETE statement removes one or more records from a table based on a condition in the WHERE clause. A simplified version of the DELETE statement is

 DELETE table_ or_view FROM table_sources WHERE search_condition 

Table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted.

If you would like to delete all rows, a fast, non-logged method is already supplied: TRUNCATE TABLE . It immediately frees all space used by indexes and data by that table, as opposed to DELETE , which should be used when partial data removal is desired.

Though both TRUNCATE TABLE and a DELETE statement with no WHERE clause remove all rows in a table, TRUNCATE TABLE is faster and uses fewer system and log resources. The DELETE statement removes rows one at a time, recording an entry in the transaction log for each row. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table's data, and only the page de- allocations are recorded in the log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, and indexes remain intact. The counter used by any identity columns is reset to the seed value for the column. If you want to retain the identity counter, use DELETE instead. The following is an example of a DELETE statement:

 DELETE from testTable WHERE phone=2802695 

Removal of data may impact other tables if cascading deletions has been specified for a relationship. This could mean the removal of other records based on a single DELETE operation. Also, a relationship definition may prevent the deletion of data and return an error condition to the operation.

Updating Data

Data that already exists may need to be modified with newer values as time passes; this type of data modification is known as updating . Data can be updated with the UPDATE statement, very much as it is deleted and inserted. An UPDATE execution is actually an INSERT and DELETE operation. The DELETE operation occurs when the old value is removed, and the INSERT occurs when the new value is added, thus creating an UPDATE effect. The basic syntax for the UPDATE statement is as follows:

 UPDATE  table_name  SET  column_name  =  expression  WHERE  condition  

Where:

  • table_name is the name of the table to be updated.

  • column_name = expression is the new value assigned to a column.

  • condition is a specified WHERE condition.

Take the query in Figure 4.7, for example, which updates the table created previously, TestTable .

Figure 4.7. UPDATE used against existing data.

graphics/04fig07.jpg

Just as with the input of data, any alterations made to the data are subject to the rules and constraints as defined in the table schema. Any data modifications made that do not meet these defined standards result in errors being generated that the front-end application may have to trap and control.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net