The
ProgrammingMSAccess.COM Site
A collection of Transact-sql scripts
for demonstration how to insert, delete, and update rows in a table.
--Set Northwind as default database,
--remove any nonstandard rows, and
--reseed IDENTITY column value to 3
USE Northwind
DELETE Shippers WHERE ShipperID > 3
DBCC CHECKIDENT(Shippers,RESEED, 3)
GO
--Insert all columns, except for IDENTITY
SELECT 'Before Insert'
SELECT * FROM Shippers
INSERT Shippers (CompanyName, Phone)
VALUES('CAB, Inc.', '(123) 456-7890')
SELECT 'After Insert'
SELECT * FROM Shippers
DELETE Shippers WHERE ShipperID > 3
--Insert all columns without list
--of column names
INSERT Shippers VALUES('CAB, Inc.', '(123) 456-7890')
SELECT 'After 2nd Insert'
SELECT * FROM Shippers
DELETE Shippers WHERE ShipperID > 3
--Insert a subset of columns with a list
--for the subset
INSERT INTO Shippers (CompanyName)
VALUES('CAB, Inc.')
SELECT 'After 3rd Insert'
SELECT * FROM Shippers
GO
--Insert IDENTITY column values along
--with other column values
DELETE Shippers WHERE ShipperID = 4
SET IDENTITY_INSERT Shippers ON
INSERT Shippers (ShipperID, CompanyName, Phone)
VALUES(4, 'CAB, Inc.', '(123) 456-7890')
SELECT 'After IDENTITY insert for ShipperID = 4'
SELECT * FROM Shippers
SET IDENTITY_INSERT Shippers OFF
GO
--Failed attempt to insert IDENTITY
--column values
DELETE Shippers WHERE ShipperID = 4
SELECT 'After delete of ShipperID = 4'
SELECT * FROM Shippers
INSERT Shippers (ShipperID, CompanyName, Phone) VALUES(4, 'CAB, Inc.', '(123)
456-7890')
SELECT 'After failed IDENTITY insert for ShipperID = 4'
SELECT * FROM Shippers
GO
--Setup Shippers table for manipulation
USE Northwind
DELETE Shippers WHERE ShipperID > 3
DBCC CHECKIDENT(Shippers,RESEED, 3)
GO
--Insert a new row for CompanyName
--and Phone columns
SET IDENTITY_INSERT Shippers ON
INSERT Shippers (ShipperID, CompanyName, Phone)
VALUES(4, 'CAB, Inc.', '(123) 456-7890')
SELECT 'After IDENTITY insert for ShipperID = 4'
SELECT * FROM Shippers
SET IDENTITY_INSERT Shippers OFF
GO
--Update CompanyName column value for new row
UPDATE Shippers
SET CompanyName = 'CAB Delivers, LLC'
WHERE ShipperID = 4
SELECT 'After CompanyName update for ShipperID = 4'
SELECT * FROM Shippers
GO
--Update both CompanyName and Phone column values
--for new row
UPDATE Shippers
SET CompanyName = 'CAB, Inc.',
Phone = '(987) 654-3210'
WHERE ShipperID = 4
SELECT 'After CompanyName and Phone updates ' +
'for ShipperID = 4'
SELECT * FROM Shippers
GO
--Insert two new rows with NULL Phone values
INSERT Shippers (CompanyName) VALUES ('CAB Delivers, LLC')
INSERT Shippers (CompanyName) VALUES ('Virginia Moved Me')
SELECT 'After Insert of NULL Phone values for two shippers'
SELECT * FROM Shippers
--Update Null values in Phone column
UPDATE Shippers
SET Phone = '(111) 111-1111'
WHERE Phone IS NULL
SELECT 'After updating NULL Phone value to flag value'
SELECT * FROM Shippers
GO
--Restore Null as Phone value for row
--with ShipperID = 5
UPDATE Shippers
SET Phone = NULL
WHERE ShipperID = 5
SELECT 'After updating Phone value to NULL ' +
'for ShipperID = 5'
SELECT * FROM Shippers
GO
--Force an error inside or outside of
--a transaction
DECLARE @DoTran Bit
SET @DoTran = 1
IF @DoTran = 1
--Two data manipulation statements where
--the second one fails inside a transaction
BEGIN
BEGIN TRANSACTION
UPDATE SHIPPERS
SET Phone = '(222) 222-2222'
WHERE ShipperID = 5
SET IDENTITY_INSERT Shippers ON
INSERT Shippers (ShipperID, CompanyName, Phone)
VALUES(6, 'CAB, Inc.', '(123) 456-7890')
IF @@ERROR <> 0
ROLLBACK TRANSACTION
SELECT 'After IDENTITY insert in transaction'
SELECT * FROM Shippers
SET IDENTITY_INSERT Shippers OFF
END
ELSE
--two data manipulation statements where
--the second one fails; no transaction
BEGIN
UPDATE SHIPPERS
SET Phone = '(222) 222-2222'
WHERE ShipperID = 5
SET IDENTITY_INSERT Shippers ON
INSERT Shippers (ShipperID, CompanyName, Phone)
VALUES(6, 'CAB, Inc.', '(123) 456-7890')
SELECT 'After IDENTITY insert without transaction'
SELECT * FROM Shippers
SET IDENTITY_INSERT Shippers OFF
END
GO
--Drop Shippers1 table in Northwind database
--if it already exists, then create and
--populate Shippers1 from the Shippers
USE Northwind
IF EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Shippers1')
DROP TABLE Shippers1
GO
--Delete any rows added to Shippers, reset
--IDENTITY, and create Shippers1 table based
--on Shippers table
DELETE Shippers WHERE ShipperID > 3
DBCC CHECKIDENT(Shippers,RESEED, 3)
SELECT ShipperID, CompanyName, Phone
INTO Shippers1
FROM Shippers
SELECT 'Initial Shippers1 rows'
SELECT * FROM Shippers1
GO
--Add two rows to the Shippers table
INSERT Shippers (CompanyName, Phone)
VALUES('CAB, Inc.', '(123) 456-7890')
INSERT Shippers (CompanyName, Phone)
VALUES('SQL Server Does It', '(987) 654-3210')
--Insert added rows from the Shippers table
--to the Shippers1 table
INSERT Shippers1
SELECT CompanyName, Phone
FROM Shippers
WHERE ShipperID > 3
--Notice order of rows
SELECT 'Shippers1 rows after two new rows'
SELECT * FROM Shippers1
SELECT 'Shippers rows after two new rows'
SELECT * FROM Shippers
GO
Want to understand SQL Server so that you can program it to
do more tasks like this? Get Programming Microsoft SQL Server 2000
with Microsoft Visual Basic .NET by Rick Dobson from Microsoft Press. Learn more about the book by clicking
here.
Copyright 2001 CAB, Inc. All rights reserved. Republication or redistribution
of CAB, Inc. content, including by framing or similar means, is expressly
prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be
liable for any errors in the content, or for any actions taken in reliance
thereon.