SQL 2005+

Basic SQL Server Data Manipulation

by Richard Carr, published at http://www.blackwasp.co.uk/SQLBasicManipulation.aspx

The seventh part of the SQL Server Programming Fundamentals tutorial uses the tutorial database created in the previous articles to take a first look at data manipulation. In this article we will create, update and delete information in SQL Server tables.

Inserting Different Data Types

As we have seen, when inserting character data the information must be provided within apostrophes whereas numeric information does not require delimiters. Each data type has different requirements, described below:

Date and Time Data

Date information can be provided in many formats, including versions with named months, such as '27 July 2008' or numeric formats such as '27/07/2008'. Numeric formats should be used with care, particularly where the application may be used in more than one country. Depending upon the local settings for the SQL Server, the format may be incorrectly interpreted. For example, '06/07/08' could be 6 July 2008, 7 June 2008 or 8 July 2006, depending upon the local interpretation. A safer option is the ISO 8601 format. This specifies that dates be provided as YYYY-MM-DD, eg. '2008-07-27' for 27 July 2008.

To comply with the ISO standard, the time portion of date and time information should be supplied in 24-hour format with the hours, minutes and seconds separated by colons, eg. '16:20:25'. If fractions of seconds are required, these can be included. You may also decide to omit the seconds or the entire time portion if these are not required.

To demonstrate the creation of date and time information try the following statements, which will create complaints. Note the use of apostrophes to delimit the dates.

INSERT INTO Complaints
    (ComplaintReference, CustomerNumber, ComplaintTime, Complaint)
VALUES
    (1, 23, '2008-07-23', 'Engineer did not arrive.')
    
INSERT INTO Complaints
    (ComplaintReference, CustomerNumber, ComplaintTime, Complaint)
VALUES
    (2, 19, '2008-07-26 14:02:25', 'Leak in radiator still present.')

UniqueIdentifer Data

Globally unique identifiers (GUIDs) are thirty-two digit hexadecimal numbers with digits organised into five groups of eight, four, four, four and twelve digits respectively. To insert a specific GUID into a uniqueidentifier column, these groups must be separated using hyphens (-) and the entire value must be delimited using apostrophes. For example:

INSERT INTO UsedParts
    (JobId, PartNumber, UnitsUsed, Cost)
VALUES
    ('12345678-1234-1234-1234-123456789ABC', '22COPIPE', 3, 2.97)

You can also generate GUIDs automatically when you wish to create new unique values.

Binary Data

It is usual to insert binary data into a database using a graphical user interface tool or application that generates the binary information and appropriate scripts in the background. However, it is possible to provide binary data directly in T-SQL scripts in SSMS. To do so, the binary is represented as a continuous set of two-digit hexadecimal bytes. To indicate that this format is being used, the string of digits is prefixed with "0x", as with the photograph column in the following example:

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (95, 'James Fields', 28.50, 0x1234567890ABCDEF)

Inserting Timestamp Data

If a table has a timestamp column it will be set automatically when a row is inserted in the table. It will also be updated automatically whenever the row is modified. The data is this type of column is completely controlled by SQL Server and may not be explicitly specified in an INSERT statement. If the column is included in the command, the value applied must be null and will be replaced with a new timestamp value.

Inserting Null Data

When you wish to indicate that the information in a column is undefined, the column value should be set to null. This is achieved using the NULL keyword, as follows. NB: In this case the column would be NULL even if it were excluded from the INSERT statement as it has no default value.

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (96, 'Rachel Moran', 28.50, NULL)

Inserting Apostrophes in Character Data

The final item to be considered in this section returns to character types. As the data to be inserted is delimited using apostrophes, an apostrophe cannot simply be included in the text being stored. Instead, the apostrophe to be added to the row should be included twice in the statement. SQL Server automatically converts the two apostrophes into a single character in the row. Try executing the following and then reviewing the table's data to demonstrate this.

INSERT INTO Engineers
    (EngineerId, EngineerName, HourlyRate, Photograph)
VALUES
    (97, 'Jack O''Leary', 28.25, NULL)