Structured Query Language 6122021 Pierce College CIS 261

Structured Query Language 6/12/2021 Pierce College CIS 261 SQL 1

Module 5: Other DML Statements Insert, Update, Select Into, and Delete 6/12/2021 Pierce College CIS 261 SQL 2

INSERT a single row of data “The following example uses the table value constructor to insert three rows into the Production. Unit. Measuretable in a single INSERT statement. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list. ” USE Adventure. Works 2012; GO INSERT INTO Production. Unit. Measure VALUES (N'FT', N'Feet', '20080414'); GO http: //technet. microsoft. com/en-us/library/ms 174335. aspx#Basic. Syntax 6/12/2021 Pierce College CIS 261 SQL 3

INSERT multiple rows of data “The following example uses the table value constructor to insert three rows into the Production. Unit. Measuretable in a single INSERT statement. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list. ” USE Adventure. Works 2012; GO INSERT INTO Production. Unit. Measure VALUES (N'FT 2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y 3', N'Cubic Yards', '20080923'); GO http: //technet. microsoft. com/en-us/library/ms 174335. aspx#Basic. Syntax 6/12/2021 Pierce College CIS 261 SQL 4

INSERT data that is not in the same order as the table columns “The following example uses a column list to explicitly specify the values that are inserted into each column. The column order in the Production. Unit. Measure table is Unit. Measure. Code, Name, Modified. Date; however, the columns are not listed in that order in column list. ” USE Adventure. Works 2012; GO INSERT INTO Production. Unit. Measure (Name, Unit. Measure. Code, Modified. Date) VALUES (N'Square Yards', N'Y 2', GETDATE()); GO http: //technet. microsoft. com/en-us/library/ms 174335. aspx#Basic. Syntax 6/12/2021 Pierce College CIS 261 SQL 5

SELECT INTO “SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it. “ To view the complete SELECT syntax, see SELECT (Transact-SQL) in SQL Books Online. ” USE NORTHWND; GO SELECT * INTO "ALFKI_ORDERS" FROM [dbo]. [Orders] WHERE Customer. ID = 'ALFKI' http: //technet. microsoft. com/en-us/library/ms 188029. aspx 6/12/2021 Pierce College CIS 261 SQL 6

SELECT INTO to create an empty table “SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it. To view the complete SELECT syntax, see SELECT (Transact-SQL) in SQL Books Online. To create a new table without DDL, just use a WHERE clause that cannot evaluate to true. The schema gets created, but no records get inserted. ” USE NORTHWND; GO SELECT * INTO [SOME_ORDERS] FROM [dbo]. [Orders] WHERE 1=2 http: //technet. microsoft. com/en-us/library/ms 188029. aspx 6/12/2021 Pierce College CIS 261 SQL 7

INSERT INTO with SELECT “Use the SELECT option to insert data from other tables. The following example shows how to insert data from one table into another table by using. The INSERT statement uses a SELECT statement to derive the data from the source table (Orders) and store the result set in the SOME_ORDERS table (created earlier). ” USE NORTHWND; GO INSERT INTO SOME_ORDERS ([Customer. ID], [Employee. ID], [Ship. Via]) SELECT [Customer. ID], [Employee. ID], [Ship. Via] FROM [dbo]. [Orders] WHERE Customer. ID = 'VINET' http: //technet. microsoft. com/en-us/library/ms 188029. aspx 6/12/2021 Pierce College CIS 261 SQL 8

UPDATE all records “The following example updates a single column for ALL ROWS in the Person. Address table. ” USE Adventure. Works 2012; GO UPDATE Person. Address SET Modified. Date = GETDATE(); http: //technet. microsoft. com/en-us/library/ms 177523. aspx#Basic. Syntax Note that this is rarely what you want to do. The next slide covers using a WHERE clause to update specific data. 6/12/2021 Pierce College CIS 261 SQL 9

UPDATE selected data using a WHERE clause “The following example uses the WHERE clause to specify which rows to update. The statement updates the value in the Color column of the Production. Product table for all rows that have an existing value of 'Red' in the Color column and have a value in the Name column that starts with 'Road-250'. ” USE Adventure. Works 2012; GO UPDATE Production. Product SET Color = N'Metallic Red' WHERE Name LIKE N'Road-250%' AND Color = N'Red'; GO http: //technet. microsoft. com/en-us/library/ms 177523. aspx#Basic. Syntax 6/12/2021 Pierce College CIS 261 SQL 10

UPDATE selected data with data from another table The following example updates data in the SOME_ORDERS table using data from the Orders table. USE Northwnd; GO UPDATE [SOME_ORDERS] SET [Freight] = o. [Freight]*10 FROM [dbo]. [Orders]o WHERE Order. ID = 10249 http: //technet. microsoft. com/en-us/library/ms 177523. aspx#Basic. Syntax 6/12/2021 Pierce College CIS 261 SQL 11

DELETE all data in a table (generally a bad idea) The following example uses the DELETE statement to delete all data in the SOME_ORDERS table USE Northwnd; GO DELETE [SOME_ORDERS] GO 6/12/2021 Pierce College CIS 261 SQL 12

DELETE selected data using a WHERE clause The following example uses the DELETE statement to delete data in the SOME_ORDERS table, but it only deleted data associated with a single customer (VINET). USE Northwnd; GO DELETE [SOME_ORDERS] WHERE Customer. ID = 'VINET' GO 6/12/2021 Pierce College CIS 261 SQL 13

TRUNCATE all data in a table (generally a better idea) The following example uses the TRUNCATE statement to remove all data in the SOME_ORDERS table. Truncate is preferred to DELETE because truncate does not write to logs, so it is much faster. You cannot recover truncated data, however, so if you make a mistake, you will need to recover the data from elsewhere. USE Northwnd; GO TRUNCATE TABLE [SOME_ORDERS] GO 6/12/2021 Pierce College CIS 261 SQL 14

This work is licensed under a Creative Commons Attribution 4. 0 International License Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The nine-college consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration, Grant #TC-23745 -12 -60 -A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 6/12/2021 Pierce College CIS 261 SQL 15