Inserting data into a SQL Server table involves using the INSERT INTO statement. This statement allows you to add new rows to a table. Below are detailed explanations and examples of inserting data, including basic inserts, inserting multiple rows, and inserting data with default values.
Basic Insert
Syntax:
INSERT INTO TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
VALUES (1, 'a', 'aa', '1999-01-01', '2023-07-01', 60000, 1);
Inserting
Multiple Rows
You can insert multiple rows using a single INSERT INTO statement by separating each row's values with a comma.
INSERT INTO
TableName (Column1, Column2, ...)
VALUES
(Value1a, Value2a, ...),
(Value1b, Value2b, ...),
...;
INSERT INTO
Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary,
DepartmentID)
VALUES
(2, 't', 'ee', '1985-05-05', '2023-07-05', 6500, 2),
(3, 'tt', 'rr', '1990-09-09', '2023-07-10', 7000, 3);
Inserting Data
with Default Values
When inserting
data, you can omit columns that have default values defined, and SQL Server
will automatically use those defaults.
Example Table
Definition with Defaults:
CREATE TABLE
Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATE DEFAULT GETDATE(),
Quantity INT DEFAULT 1,
ProductID INT
);
Inserting Data
Using Default Values:
INSERT INTO
Orders (ProductID)
VALUES (101); --
OrderDate and Quantity will use their default values
Inserting Data
with NULL Values
If a column
allows NULL values, you can explicitly insert NULL into that column.
INSERT INTO
Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary,
DepartmentID)
VALUES (4, 'r', 'r', NULL, '2023-07-15', 72000, NULL); -- BirthDate and
DepartmentID will be NULL
Inserting Data
into Identity Columns
If a table has an
identity column (auto-increment), you typically don't insert a value into this column directly. SQL Server handles it automatically. However, if necessary, you can use the SET IDENTITY_INSERT statement.
Example Table Definition:
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
Inserting Data Without Specifying Identity Column:
sql
Copy code
INSERT INTO Customers (FirstName, LastName)
VALUES ('Alice', 'Jones');
Inserting Data with Specifying Identity Column:
SET IDENTITY_INSERT Customers ON;
INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'b', 'bb');
SET IDENTITY_INSERT Customers OFF;
Inserting Data from Another Table
You can insert data into a table from another table using a SELECT statement.
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
SELECT EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID
FROM OldEmployees
WHERE HireDate > '2020-01-01';
Using OUTPUT Clause
The OUTPUT clause returns the rows that were modified by the INSERT statement.
INSERT INTO Employees (FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName
VALUES ('c', 'c', '1999-02-02', '2023-07-20', 80000, 4);
Handling Errors
with TRY...CATCH
To handle potential errors during data insertion, you can use a TRY...CATCH block.
BEGIN TRY
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary, DepartmentID)
VALUES (2, 'd', 'd', '1999-03-03', '2023-07-25', 75000, 2);
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;