How to insert data into table sql

By FoxLearn 6/18/2024 8:15:16 AM   29
To insert data into a table in SQL, you typically use the INSERT INTO statement.

Here’s a basic syntax and example of how to insert data into table sql.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

- table_name is the name of the table you want to insert data into.

- (column1, column2) specifies the columns into which data will be inserted.

- (value1, value1) are the values being inserted into the respective columns.

For example: We have a table named Users with columns UserId, UserName, Email. In which, the UserId column automatically increases in value.

First, Run the below sql script to create users table.

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](25) NULL,
	[Email] [varchar](255) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Next, You can run the sql script below to insert a new row into this table.

INSERT INTO Users (UserName, Email)
VALUES ('admin', '[email protected]')

You can also insert multiple rows in a single INSERT INTO statement by listing multiple sets of values separated by commas.

INSERT INTO Users (UserName, Email)
VALUES
('admin', '[email protected]'),
('fox', '[email protected]'),
('tom', '[email protected]')

If you want to insert data into all columns of a table and in the same order as they are defined in the table schema, you can omit the column names:

INSERT INTO Users
VALUES ('test', '[email protected]')

You need to provide values for all columns in the order they appear in the table (UserName, Email in this case).

Ensure that the data types of the values you are inserting match the data types of the columns in the table. If a column allows NULL values and you do not specify a value for it, the default NULL will be inserted.

Primary keys and unique constraints ensure that you cannot insert duplicate rows based on those columns.