SQL INSERT INTO Statement

The SQL INSERT INTO statement is used to add a new record or rows of column data in a specific table.

It is also is used to add one or multiple new rows or records at once in a table.

Note: Most of the databases will not supports inserting multiple records at once.

For referential integrity, MySQL supports foreign keys, but not the CHECK constraint. However, we can use triggers to imitate them.

BEFORE INSERT and BEFORE UPDATE are two MySQL triggers that can be used to simulate CHECK restrictions.

Suppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows:

Create table car (number char(9));

Insert into car values('AB-235-YZ');

The above value is a valid one but what about the value that we are going to insert in the next query.

insert into car values('AB-2X5-YZ');

The above number is invalid because it contains a character between digits, which is incompatible with the fixed syntax we're employing.

Creating BEFORE INSERT trigger to emulate CHECK CONSTRAINT for inserting the values :

Now, we can create a trigger as follows to prevent such kind of insertion :

mysql> delimiter //
mysql> create trigger car_insert_value before insert on car
    -> for each row
    -> begin
    ->     if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    ->     then
    ->         signal sqlstate '45000' set message_text = 'Not a valid Number';
    ->     end if;
    -> end //

mysql> Delimiter ;

mysql> Delete from car;

Now, if we try to insert an invalid number, the above-created trigger will prevent us from doing so and will throw the following error:

mysql> insert into car values('AB-2X5-YZ');

Output:

But, we can insert the valid values as follows:

mysql> insert into car values('AB-235-YZ');

Example 2: First, create a new table named parts for the demonstration:

CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

Next, create a stored procedure to check the values in the cost and price columns.

DELIMITER $

CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
    IF cost < 0 THEN
        SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
    END IF;
    
    IF price < 0 THEN
	SIGNAL SQLSTATE '45001'
	   SET MESSAGE_TEXT = 'check constraint on parts.price failed';
    END IF;
    
    IF price < cost THEN
	SIGNAL SQLSTATE '45002'
           SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
    END IF;
END$
DELIMITER ;

Then, create BEFORE INSERT and BEFORE UPDATE triggers. Inside the triggers, call the check_parts() stored procedure.

-- before insert
DELIMITER $
CREATE TRIGGER `parts_before_insert` BEFORE INSERT ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$   
DELIMITER ; 

-- before update
DELIMITER $
CREATE TRIGGER `parts_before_update` BEFORE UPDATE ON `parts`
FOR EACH ROW
BEGIN
    CALL check_parts(new.cost,new.price);
END$   
DELIMITER ;

After that, insert a new row that satisfies all the following conditions:

cost > 0
And price > 0
And price  >= cost

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',100,120);

The INSERT statement invokes the BEFORE INSERT trigger and accepts the values.

The following INSERT statement fails because it violates the condition: cost > 0.

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',-100,120);

The following INSERT statement fails because it violates the condition: price > 0.

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-002','Heater',100,-120);

The following INSERT statement fails because it violates the condition: price > cost.

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-003','wiper',120,100);

Let’s see what we are having now in the parts table.

SELECT * FROM parts;

MySQL CHECK Constraint Emulation Example

The following statement attempt to update the cost to make it lower than the price:

UPDATE parts
SET price = 10
WHERE part_no = 'A-001';

Data validation is a process for ensuring that data is accurate and of good quality. Multiple people or processes are continually updating, deleting, querying, or moving data in databases, thus ensuring that data is accurate at all times is critical. In this tutorial, we'll show you how to create some basic SQL validation rules.

It's usually done before adding, altering, or processing data. Similarly, when we seek to combine data from several sources, we typically talk about 'cleaning' or validating the data. When validating data, we can see if it meets the following criteria:

• finished (ie no blank or null values)
• unique (i.e. no duplicate values) and in line with our expectations (eg a decimal between a certain range)

1. Constraints in SQL

SQL Server constraints are rules that limit the amount of data that can be entered into our tables. These restrictions help maintain the database's integrity by ensuring the data's correctness. Constraints can be defined before or after tables are formed, and they can apply to single or many columns.

SQL Server successfully inserts data into the column if it matches the constraint rule conditions. The insert statement is aborted with an error message if data violates the constraint.

2. NOT NULL Constraint

NULL values, which stand for 'unknown value,' are allowed in SQL Server. There are appropriate use cases for NULLS, but there are also apparent scenarios when a NULL value cannot be accepted. We can define a NOT NULL constraint on a column in these circumstances.

We're building an employees table in the example below, and we've specified that all columns except 'MiddleName' will not allow NULLs.

CREATE TABLE Employees
(
EmployeeID  INT NOT NULL,
FirstName Varchar(100) NOT NULL,
MiddleName Varchar(50) NULL,
LastName Varchar(100) NOT NULL,
Gender char(1) NOT NULL,
Address Varchar(200) NOT NULL
);

If we need to make existing columns NOT NULL, we can either use an ALTER TABLE statement or a SELECT statement. Instead, we may utilise SQL Server Management Studio (SSMS) to make the modifications by right-clicking on the table and selecting 'Design'.

3. UNIQUE Constraint

On ID columns, we usually apply the UNIQUE constraint. We're going to make a basic table and state that 'EmployeeID' must be unique in the example below (and not NULL).

CREATE TABLE Employees
(
EmployeeID INT NOT NULL UNIQUE,
FirstName Varchar(100) NOT NULL,
MiddleName Varchar(50) NULL,
LastName Varchar(100) NOT NULL,
Gender char(1) NOT NULL,
Address Varchar(200) NOT NULL
);

4. CHECK Constraint

A check constraint is a logical expression that is used to determine which values are valid. In a payroll database, for example, we could want to specify a maximum figure that can be input. When establishing a table, the syntax for the CHECK constraint is shown below.

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
 ...
CONSTRAINT constraint_name
CHECK (column_name condition)
);

So, for our payroll example, we could use the following to create a table that enforces a check constraint on values entered into the Salary column:

CREATE TABLE dbo.Payroll
(
EmployeeID int PRIMARY KEY,
EmployeeType INT,
Salary decimal(9,2),
CONSTRAINT CK_Payroll_Salary_1 CHECK (EmployeeType = 1 and Salary > 0  and Salary < 200000.00)
);

The ALTER statement can be used to add a check constraint to a column in an existing table:

ALTER TABLE dbo.Payroll
ADD CONSTRAINT CK_Payroll_Salary_1
CHECK (EmployeeType = 1 and Salary > 0  and Salary < 200000.00);

And to remove a check constraint we can use the following:

ALTER TABLE dbo.Payroll
DROP CONSTRAINT CK_Payroll_Salary_1;

Finally, temporarily enabling or disabling check restrictions is frequently handy, and we can do it as follows: To enable a check constraint, do the following:

ALTER TABLE dbo.Payroll
WITH CHECK CHECK CONSTRAINT CK_Payroll_Salary_1;

To disable a check constraint:

ALTER TABLE dbo.Payroll
NOCHECK CONSTRAINT CK_Payroll_Salary_1;

As you can see, Check Constraints are easy to create and flexible in terms of usage.

5. How to Add Validation in SQL Spreads

The SQL Spreads Excel Add-in makes updating SQL Server data from Excel simple. Using a data post-processing script, it's also simple to implement SQL data validation criteria from within Excel.

The Data Post-processing script is a SQL query that will be run at the end of the transaction to update the database with the modifications made in Excel. SQL Spreads will always validate all entered values against SQL Server's Data Types by default. The procedure outlined below is a more complex version of the CHECK constraint we discussed earlier.

In SQL Spreads, open Document Settings and click the Edit Post-Save SQL Query button to create a Data Post-processing script.

6. SQL Spreads Documents Settings

Our validation script may now be entered in the Post-Save SQL Query dialogue box. The validation script includes the logic we want to verify as well as an error message that can be displayed to the user.

The SQL query below checks to see if any of the numbers entered in the Payroll table's Salary column are incorrect (ie less than or equal to 0 or greater than 200000 for Employees of type 1).

IF (
SELECT COUNT([Salary]) 
FROM [demo].[dbo].[Payroll] 
WHERE EmployeeType = 1 AND (Salary <= 0  OR Salary > 200000.00)) 
> 0   
RAISERROR('The Salary for Employee_Type 1 must be between 0 and 200000',16,1);

SQL Spreads Post-Save SQL Query

If a user attempts to save to the database with an erroneous value in the Salary column, the following message will appear, and the update transaction will be rolled back.

The INSERT... DEFAULT VALUES statement in SQL inserts a single record with only DEFAULT values for each row.

If any column is defined with default values when a table is created, we can use the keyword 'DEFAULT' in the INSERT statement to take the default value for that column.

Example 1: To understand this let's take an example. Let's create a table with the default value (Just to let you know we are using SQL Server) :

CREATE TABLE #CSharpRegistration (Id INT IDENTITY(1,1),
FirstName VARCHAR(250) DEFAULT 'Registration fname',
LastName VARCHAR(250) DEFAULT 'Registration Lname')

Now, once the table is created you might be wondering what's new in this, it has a default value and identity column. There is nothing much in creating a statement but magic exists in the below statement.

INSERT INTO #CSharpRegistration (FirstName,LastName) VALUES (DEFAULT,DEFAULT)

After you've written and run the aforementioned insert statement, In the table, you'll notice that a default row has been added. Run the SQL statement below to see whether it works.

SELECT * FROM #CSharpRegistration

Example 2: It is written as such:

INSERT INTO AUTHOR
DEFAULT VALUES;
 create.insertInto(AUTHOR)
      .defaultValues()
      .execute();

When you wish to "reserve" a row in the database for a subsequent UPDATE command within the same transaction, this makes a lot of sense. Or if you simply want to send an event with default data generated by the trigger, such as IDs or timestamps.

Although not all databases offer the DEFAULT VALUES clause, jOOQ can imitate it with the following statement:

INSERT INTO AUTHOR 
    (ID, FIRST_NAME, LAST_NAME, ...)
VALUES (
	DEFAULT, 
	DEFAULT, 
	DEFAULT, ...);
 create.insertInto(
        AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, ...)
      .values(
      	defaultValue(AUTHOR.ID), 
      	defaultValue(AUTHOR.FIRST_NAME), 
      	defaultValue(AUTHOR.LAST_NAME), ...)
      .execute();

Individual columns can also be given the DEFAULT keyword (or the DSL#defaultValue() method), but this has the same effect as removing the column entirely.

Example 3: For example, we have created a table ‘employee’ with a default value of column ‘DOJ’ as follows:

Create table employee(id int, name varchar(20), doj date DEFAULT '2005-01-01');

Insert into employee(id, name, doj) values(1, ’Aarav’, DEFAULT);

select * from employee;

+------+------------+---------------+
| id   | name       | doj           |
+------+------------+---------------+
| 1    |Aarav       | 2005-01-01    |
+------+------------+---------------+

From the query above, it can be observed that while inserting the values we use DEFAULT keyword, MySQL insert the default value specified at the time of defining the column.

In MySQL, insert a record IF IT NOT EXISTS. To accomplish this, we'll use MySQL subqueries using the NOT EXISTS clause.

We can accomplish it with a subquery if you truly need to do it with an INSERT statement in SQL.

When you're inserting records into a database, you might want to double-check that they don't already exist. Duplicate records in your table may be undesirable.

Example 1: Let's begin by creating the data that will be used throughout. We'll make a table called customer_details and populate it with data.

CREATE TABLE customer_details (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    customer_address VARCHAR(255),
    primary key (customer_id));

INSERT INTO customer_details (customer_name,customer_address) 
 VALUES("Gustav","56A Denmark"),
("Henric","255 USA"),
("Richa","78 India"),
("Margit","58 Canada"),    
("Henric","18 Libya"),
("Henric","43 Sweden"),
("Richa","78 Singapore"),
("Henric","255 Italy"),
("Rohit","899 Bangladesh");

1. INSERT NOT EXISTS Syntax

NOT EXISTS is FALSE if a subquery returns any rows at all. It means that if the NOT EXIST clause's subquery is TRUE, no records will be returned.

Syntax:-

INSERT INTO your_table_name (column1, column2, ....)
SELECT * FROM (SELECT value1, value2,....) AS temp
WHERE NOT EXISTS (< conditional_subquery>);

Parameters:

• your_table_name – is the name of your table where you want to insert the data.
• column1, column2, …. – is the column list in your_table_name.
• < conditional_subquery> – is the sub-query including a select statement to get the row with a particular condition.

2. INSERT single record if NOT EXISTS in MySQL

We'll enter a record into the table customer_details in this part, but first we'll see if the customer_name already exists. If the answer is yes, the record will not be included; otherwise, it will.

Let us look into the below example query to have a better understanding.

INSERT INTO customer_details (customer_name,customer_address)
SELECT * FROM (SELECT 'Veronica' AS customer_name, '552 NewYork USA' AS customer_address) AS temp
WHERE NOT EXISTS (
    SELECT customer_name FROM customer_details WHERE customer_name = 'Veronica'
) LIMIT 1;

Action Message Output Response:-

We are picking the record from the customer_details in the subquery with the NOT EXISTS condition. FALSE will be returned if the row does not exist in the table. The query will INSERT the row because there is a 'NOT' keyword before the EXISTS keyword.

SELECT * FROM customer_details;

Since the record exists in the table with customer_name=’Veronica‘ , let us again try and insert the record with the same customer_name. Observe the below query and response message.

INSERT INTO customer_details (customer_name,customer_address)

SELECT * FROM (SELECT 'Veronica' AS customer_name, '552 NewYork USA' AS customer_address) AS temp
WHERE NOT EXISTS (
    SELECT customer_name FROM customer_details WHERE customer_name = 'Veronica'
) LIMIT 1;

Action Message Output Response:-

This time the record was not inserted as customer_name ‘Veronica’ already existed in the table customer_details.

3. INSERT multiple record if NOT EXISTS in MySQL

What if there are multiple records to be inserted, and we want to make sure that no record with the same column value already exists.

Let's say you want to add two rows for customers 'Suveer' and 'Jenefir' only if the records with those names don't already exist. For the answer, look at the query below.

INSERT INTO customer_details (customer_name, customer_address)

SELECT customer_name, customer_address
FROM
(
  SELECT customer_name , customer_address
  FROM
  (
     SELECT 'Suveer' as customer_name , '28 Street North America' as customer_address 
  ) AS temp_1
  WHERE NOT EXISTS
  (
     SELECT customer_name FROM customer_details WHERE customer_name = 'Suveer'
  )
  UNION ALL
  SELECT customer_name, customer_address
  FROM
  (
     SELECT 'Jenefir' as customer_name , '28 Canada' as customer_address 
  ) AS temp_2
  WHERE NOT EXISTS
  (
     SELECT customer_name FROM customer_details WHERE customer_name = 'Jenefir'
  )
) alias_customer_details;

The idea is the same as having a subquery within the NOT EXISTS clause with a second UNION query to join the INSERTS together.

SELECT * FROM customer_details;

Example 2: Let’s say we have our student table again, and it has some records.

STUDENT_ID	FIRST_NAME	LAST_NAME	FEES_REQUIRED	FEES_PAID	ENROLMENT_DATE	GENDER
1		John		Smith		500		100		01/Feb/15	M
2		Susan		Johnson		150		150		12/Jan/15	F
3		Tom		Capper		350		320		06/Mar/15	M
4		Mark		Holloway	500		410		20/Jan/15	M
5		Steven		Webber		100		80		09/Mar/15	M
6		Julie		Armstrong	100		0		12/Feb/15	F
7		Michelle	Randall		250				23/Jan/15	F
8		Andrew		Cooper		800		400		04/Mar/15	M
9		Robert		Pickering	110		100		30/Jan/15	M
10		Tanya		Hall		150		150		28/Jan/15	F
11		Jarrad		Winston		700		300		(null)		(null)
12		Mary		Taylor		500		100		(null)		F

Now, we have our new_student table, which has some more records.

STUDENT_ID	FIRST_NAME	LAST_NAME	FEES_REQUIRED	FEES_PAID	ENROLMENT_DATE	GENDER
1		Mark		Anderson	860		45		M
2		John		Rogers		210		700		M
3		Susan		Johnson		500		0		F

But, there are some duplicate records – Susan Johnson.

What is the definition of a duplicate record? That is the key question here.

You must first declare which columns define a duplicate record. Let's pretend that the first name and last name columns are used to identify duplicate records (this doesn't account for two students with the same name, but it's just an example).

We'd have to build an INSERT statement that inserts our data while also using a WHERE clause to ensure that they don't already exist in the table.

Our query could look like this:

INSERT INTO student
(student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender)
SELECT
student_id, first_name, last_name, fees_required, fees_paid, enrolment_date, gender
FROM new_student
WHERE NOT EXISTS (
  SELECT *
  FROM student
  WHERE student.first_name = new_student.first_name
  AND student.last_name = new_student.last_name
);

This query will insert the values from the new_student table into the student table, where the first_name and last_name records to not match.

Let’s see what this table now shows.

SELECT * FROM student;

STUDENT_ID	FIRST_NAME	LAST_NAME	FEES_REQUIRED	FEES_PAID	ENROLMENT_DATE	GENDER
1		John		Smith		500	100	01/Feb/15	M
2		Susan		Johnson		150	150	12/Jan/15	F
3		Tom		Capper		350	320	06/Mar/15	M
4		Mark		Holloway	500	410	20/Jan/15	M
5		Steven		Webber		100	80	09/Mar/15	M
6		Julie		Armstrong	100	0	12/Feb/15	F
7		Michelle	Randall			250	23/Jan/15	F
8		Andrew		Cooper		800	400	04/Mar/15	M
9		Robert		Pickering	110	100	30/Jan/15	M
10		Tanya		Hall		150	150	28/Jan/15	F
11		Jarrad		Winston		700	300	(null)		(null)
12		Mary		Taylor		500	100	(null)		F
100		Mark		Anderson	860	45	(null)		M
102		John		Rogers		210	700	(null)		M

You can see the student table has been updated.

As I previously stated, the optimal approach to do this is to use a MERGE statement, however if you need to insert values that do not exist using INSERT, this is how you can do it.

Insert autoincrement id

The DEFAULT keyword can be used instead of the value in the value-list equivalent to the autoincrement column in the column list in the Put INTO statement to insert data into a table with an auto increment column.

If your column has an auto incremented primary key, you won't have to specify a value for it in the INSERT statement. This indicates that MySQL will return the value for that particular column.

The syntax is as follow:

INSERT INTO table_name (autoincrement_column, column2, column3, ...)
VALUES (DEFAULT, value2, value3, ...);

Alternatively, you can skip specifying the autoincrement column's column name in the column-list, obviating the requirement to specify a value in the value-list in the INSERT INTO statement.

Example 1: To understand the above concept, let us first create a table. The query to create a table is as follows:

mysql> create table AutoIncrementedPrimary
   -> (
   -> Id int auto_increment,
   -> Name varchar(100),
   -> Age int,
   -> Primary key(Id)
   -> );

Now only insert entries for the columns Name and Age; because the Id column is configured to auto increment, MySQL will provide the value for it. The insert record query is as follows:

mysql> insert into AutoIncrementedPrimary(Name,Age) values('John',23);
mysql> insert into AutoIncrementedPrimary(Name,Age) values('Sam',24);
mysql> insert into AutoIncrementedPrimary(Name,Age) values('Carol',30);
mysql> insert into AutoIncrementedPrimary(Name,Age) values('Johnson',28);

Let us now display all records from the table using select command. The query is as follows:

mysql> select *from AutoIncrementedPrimary;

Output :

+----+---------+------+
| Id | Name    | Age  |
+----+---------+------+
|  1 | John    |   23 |
|  2 | Sam     |   24 |
|  3 | Carol   |   30 |
|  4 | Johnson |   28 |
+----+---------+------+

Example 2:

SELECT * FROM player_data;

INSERT INTO player_data (p_id, player_name, gender, country, weight_kg, height_cm)
VALUES (DEFAULT, 'Nelson Croser', 'Female', 'Czech Republic', 75, 135)
RETURNING p_id;

SELECT * FROM player_data;

CREATE TABLE player_data (
p_id SERIAL PRIMARY KEY,
player_name VARCHAR(50),
gender VARCHAR(50),
country VARCHAR(50),
weight_kg INT,
height_cm INT);

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Elsy Maharry', 'Male', 'United States', 63, 181);

INSERT INTO player_data (player_name, gender, country, weight_kg, height_cm)
VALUES ('Rockie Ethridge', 'Female', 'Canada', 67, 179)
RETURNING p_id;

SELECT * FROM player_data;

This is how to insert into table with auto_increment in PostgreSQL.

To bulk import data from a data file into a SQL Server, use the SQL BULK INSERT and the INSERT...SELECT * FROM OPENROWSET(BULK...) statements. This section also goes over the security implications of utilising BULK INSERT and OPENROWSET(BULK...) to bulk import data from a remote location.

Note: It's essential to comprehend how SQL Server handles impersonation when using BULK INSERT or OPENROWSET(BULK...).

Syntax:

Most commonly used syntax with the below arguments:

/ * - - - - - - -  -  Bulk insert query is given below - - - - - */
BULK INSERT    { database_name.schema_name.table_or_view_name |
schema_name.table_or_view_name | table_or_view_name }
FROM 'data_file_name'
[   [ WITH
( [  ,  FORMAT = 'CSV' ] [ , FIRSTROW = ‘first_row’  ] 
[  , FIELDQUOTE = 'quote_characters'] [ , FORMATFILE = 'format_file_path' ] 
[  ,  FIELDTERMINATOR = 'field_terminator' ] [  ,  ROWTERMINATOR = 'row_terminator' ] )]

We have other parameters that can be mentioned as below:

,BATCHSIZE: batch size
, DATA_SOURCE: ‘data source name’
,ERRORFILE: ‘file name’
, ROWS_PER_BATCH: rowsperbatch
, ROWTERMINATOR: ‘row terminator’
,TABLOCK
,CHECK_CONSTRAINTS
, CODEPAGE: { ‘RAW’ }
, DATAFILETYPE: { ‘char’ }
, ERRORFILE_DATA_SOURCE: ‘data sourcename’
,FIRSTROW: first row
,FIRE_TRIGGERS
, FORMATFILE_DATA_SOURCE: ‘data sourcename’
,KEEPIDENTITY
,KEEPNULLS
, KILOBYTES_PER_BATCH: kilobytes perbatch
,LASTROW: last row
,MAXERRORS: max errors
,ORDER ( { column [ ASC | DESC ] } [ ,…n ] )

1. BULK INSERT statement

BULK INSERT inserts data from a file into a table in bulk. The in option of the bcp command provides comparable capability; however, the data file is read by the SQL Server activity.

2. OPENROWSET(BULK...) Function

By using the OPENROWSET function with the BULK option, you can access the OPENROWSET bulk rowset provider. The OPENROWSET(BULK...) function allows you to access remote data by using an OLE DB provider to connect to a remote data source, such as a data file.

Call OPENROWSET(BULK...) from a SELECT...FROM clause within an INSERT statement to bulk import data. The following is the basic syntax for bulk data import:

3. INSERT ... SELECT * FROM OPENROWSET(BULK...)

OPENROWSET(BULK...) accepts table hints when used in an INSERT query. The BULK clause can accept the following specialised table hints in addition to the usual table hints, such as TABLOCK: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY.

4. Security considerations

When a user logs in with a SQL Server login, the SQL Server process account's security profile is used. It is not possible to validate a SQL Server login outside of the Database Engine. As a result, when a login using SQL Server authentication initiates a BULK INSERT command, the connection to the data is made using the SQL Server process account's security context (the account used by the SQL Server Database Engine service).

You must provide the SQL Server Database Engine account access to the source data in order for it to read it successfully. When a SQL Server user connects in with Windows Authentication, the user can only read files that the user account can acquire, regardless of the SQL Server process's security profile.

Consider the case of a user who used Windows Authentication to log into a SQL Server instance. The user account must have read access to the data file in order to use BULK INSERT or OPENROWSET to import data from it into a SQL Server table. Even if the SQL Server process does not have access to read the data file, the user who has access to it can import data from it into a table. The SQL Server process does not require the user to give it file-access privilege.

By transmitting the credentials of an authenticated Windows user, SQL Server and Microsoft Windows can be configured to allow one instance of SQL Server to connect to another instance of SQL Server. Impersonation or delegation is the term for this arrangement. When using BULK INSERT or OPENROWSET, it's critical to understand how SQL Server versions manage security for user impersonation. The data file might be stored on a different machine than the SQL Server process or the user thanks to user impersonation.

For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C.

5. Bulk importing to SQL Server from a remote data file

The data file must be shared between the two computers if you want to use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another machine. Use the universal naming convention (UNC) name for a shared data file, which takes the form \\Servername\Sharename\PathFilename. Furthermore, the account that is used to access the data file must have the rights necessary to read the file from the remote disc.

The following BULK INSERT statement, for instance, bulk imports data from a data file named newdata.txt into the AdventureWorks database's SalesOrderDetail table. This data file is located in a shared folder called \dailyorders on a network share directory called salesforce on a computer2 system.

SQL:

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Note: Because the client reads the file independently of SQL Server, this restriction does not apply to the bcp utility.

6. Bulk importing from Azure Blob storage

Construct a DATABASE SCOPED CREDENTIAL based on an SAS key that is encrypted with a MASTER KEY when importing from Azure Blob storage when the data is not public (anonymous access), and then create an external database source for usage in your BULK INSERT operation.

Note: If you use explicit transaction, you'll get an error 4861.

7. Using BULK INSERT

The example below shows how to use the BULK INSERT command to load data from a csv file into an Azure Blob storage location where an SAS key has been generated. An external data source is set for the Azure Blob storage location. This necessitates a database-scoped credential with a shared access signature encrypted with the user database's master key.

SQL

Optional - if a DATABASE SCOPED CREDENTIAL is not required since the blob is configured for public (anonymous) access, a MASTER KEY is not required!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

NOTE: Make sure that you don't have a leading ? in SAS token, and that you have at least read permission on the object that should be loaded srt=o&sp=r, and that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> 
          CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

8. Using OPENROWSET

The OPENROWSET command is used in the following example to import data from a csv file into an Azure Blob storage location where an SAS key has been generated. An external data source is set for the Azure Blob storage location. This necessitates a database-scoped credential with a shared access signature encrypted with the user database's master key.

SQL

Optional - if a DATABASE SCOPED CREDENTIAL is not required since the blob is configured for public (anonymous) access, a MASTER KEY is not required!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
GO

Optional - a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

NOTE: Make sure that you don't have a leading ? in SAS token, and that you have at least read permission on the object that should be loaded srt=o&sp=r, and that expiration period is valid (all dates are in UTC time)

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> 
          CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

INSERT INTO achievements with (TABLOCK) (id, description)

SELECT * FROM OPENROWSET(
   BULK  'csv/achievements.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   FORMAT ='CSV',
   FORMATFILE='csv/achievements-c.xml',
   FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
    ) AS DataFile;

Example 1: Create table script:

Run BULK INSERT Command

Because our source and target are both ready, we can now execute the BULK INSERT command. We've already covered syntax, so you should be able to figure out what we're trying to accomplish with the script below. Check that the path where you saved your CSV file is correct. Maximum errors have been set to 2 for testing purposes.

BULK INSERT Sales
FROM 'C:\temp\1500000 Sales Records.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n',
    BATCHSIZE=250000,
    MAXERRORS=2);	

If you run this script, you'll see the problems listed below. The main issue is that the target schema has an extra field (Created Date) that is not present in the source model. As a result, we received many failures throughout the load, and after two errors, the load process was terminated because it exceeded our 2 error maximum threshold.

Bulk Insert Example

If you delete the max error option, the programme will halt at 10 errors because the default value will be used. Where each row in the load is going to fail, the max error option can be quite handy.

Bulk Insert Errors

We discovered right at the start of the tip that we can load data into a table or view using the syntax. So, as seen below, let's create a view with the same schema as the source file. I titled the view SalesView so that we can be sure we're bulk loading data from the view rather than the database during the load.

CREATE VIEW SalesView
AS
SELECT [Region]
      ,[Country]
      ,[ItemType]
      ,[SalesChannel]
      ,[OrderPriority]
      ,[OrderDate]
      ,[OrderID]
      ,[ShipDate]
      ,[UnitsSold]
      ,[UnitPrice]
      ,[UnitCost]
      ,[TotalRevenue]
      ,[TotalCost]
      ,[TotalProfit]
  FROM [dbo].[Sales]

Create View Script

Run the same script again after creating the view, but this time replace the table name with the view name. It may take a few seconds for the data to load in increments of 250,000 once you run the script. This is a result of the BATCHSIZE value we set while running the BULK INSERT query.

BULK INSERT SalesView
FROM 'C:\temp\1500000 Sales Records.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n',
    BATCHSIZE=250000);	

Bulk Insert to ingest data in batches

After the data has been inserted, you may choose a few entries to see that the data has been imported as intended and that the creation date field has no values because we didn't supply a default value or a computed formula for it.

SELECT TOP 5 * FROM [dbo].[Sales]

Sample View of Ingested Data

In this approach, the BULK INSERT command can be used to quickly and easily import data from an external source into SQL Server with only one statement.

Example 2: How Bulk Insert in SQL:

To have a better understanding of BULK INSERT, I downloaded a file containing a significant quantity of data and attempted to load it into SQL. It is divided into ten rows in the file. Let us now execute a bulk load. The table we designed for loading large amounts of data is shown below.

Code:

create table bus_index_price
(
series_reference_default varchar(20),
period_value decimal(10,3),
data_value int,
current_status varchar(10),
units varchar(10),
subject_value varchar(30),
group_value varchar(30),
series_title_1 varchar(20),
series_title_2 varchar(20),
series_title_3 varchar(20),
series_title_4 varchar(20),
series_title_5 varchar(20)
);

Now let us bulk insert the data into the table:

/ * - - - - - - -  -  Bulk insert query is given below - - - - - */
create view sample_V
as
select
series_reference_default
,period_value
,data_value
,current_status
, units
,subject_value
,group_value
, series_title_1
, series_title_2
, series_title_3
, series_title_4
, series_title_5
FROM bus_index_price
GO
BULK INSERT bus_index_price
FROM'D:\sample.txt'
WITH
(
FIRSTROW= 2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n'
);

Example 3: Now let us see and another example and insert a bulk amount of data:

create table Alphabet
(
alphabet_data varchar(10),
data_value int
)

Now let us bulk insert the data into the table: –

create view alphabet_v1
as
select
alphabet_data
,data_value
FROM alphabet
GO
BULKINSERT alphabet
FROM 'D:\alphabet.txt'
WITH
(
FIRSTROW= 2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\n'
);

With the INSERT command, the SQL case statement can be used.

The CASE keyword is written first, indicating the start of the CASE conditional phrases. Then, in the WHEN and THEN parts, we provide the condition and the result.

Because the condition in the WHEN section produces a boolean result, it is followed by the result in the THEN part if it evaluates to TRUE. If it returns FALSE, further WHEN conditions are verified; otherwise, the ELSE clause's result is applied. Finally, we use the END keyword to finish the CASE statement.

Example 1: With the use of SQL's Case statement, we may also put data into SQL tables. Let's say we have a programme that inserts data into the Employees database. For gender, we get the following results.

Value
0

Description - Required value in Employee table:

Male Employee

M
1

Female Employee

F

For Male and Female employees, we do not wish to use the values 0 and 1. For employee gender, we must enter the required numbers M and F.

We used variables to hold column values in the following query. We're utilising a Case statement in the insert statement to define the corresponding value to enter in the employee table. It checks for required values in the Case statement and puts values from the THEN expression into the table.

Declare @EmployeeName varchar(100)
Declare @Gender int
Declare @Statecode char(2)
Declare @salary money
Set @EmployeeName='Raj'
Set @Gender=0
Set @Statecode='FL'
set @salary=52000
 
Insert into employee
values 
(@EmployeeName,
CASE @Gender
WHEN 0 THEN 'M'
WHEN 1 THEN 'F'
end,
@Statecode,
@salary)

In the following screenshot, we can see the newly inserted row contains Gender M instead of value 0.

Example 2: CASE with INSERT Statement:

You can use the SQL case statement with the INSERT statement.

Here, I have given a simple example to insert a single value in students table.

SET mark=300;
INSERT INTO students(name, roll_number, mark) VALUES('Aayush Aryan',2106,
CASE mark
    WHEN 400 THEN 80%
    WHEN 300 THEN 60%
    WHEN 200 THEN 40%
    WHEN 100 THEN 20%
    ELSE mark
END 
);

Example 3: Implementation of the CASE expression in PostgreSQL, First we will create a table:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Now we will insert some data in this table using the INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

CASE When example - Data inside Table

Now we will use the CASE expression like this in our example:

SELECT Emp_ID, Name,
CASE
WHEN Salary >= '3000' THEN 500
WHEN Salary >= '2500' THEN 750
WHEN Salary >= '2000' THEN 1000
END as Increment
From Employee;

We utilised the CASE conditional expressions in this example to calculate the increment for each employee based on their income. Employees with a salary of '3000 or above' will receive a $500 raise, while those with a salary of '2500 or above' and '2000 and above' will earn raises of '$750' and '$1000,' accordingly.

Date in SQL Server

Create a Date column/variable with no time information by using the Date Data type. The time is set to 00:00:00 by default, and SQL Server does not store it. As a result, since there is no requirement to keep time, it is a very effective approach to store date.

Storage 			size & Range

Min Date			0001-01-01
Max Date			9999-12-31
Storage				3 Bytes
Default Value			1900-01-01
Default string literal format	YYYY-MM-DD

Along with strings and numbers, date and/or time values are frequently stored in databases, such as a user's birth date, an employee's hire date, the date and time a specific entry in a table is created or edited, and so on.

Temporal data is the name given to this type of data, and every database engine has a default storage format and data types for it. The data formats supported by the MySQL database server for managing dates and times are listed in the table below.

Type		Default format			Allowable values
DATE		YYYY-MM-DD			1000-01-01 to 9999-12-31
TIME		HH:MM:SS or HHH:MM:SS		-838:59:59 to 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS		1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD HH:MM:SS		1970-01-01 00:00:00 to 2037-12-31 23:59:59
YEAR		YYYY				1901 to 2155

The format for DATE values is YYYY-MM-DD, with YYYY representing the whole year (4 digits) and MM and DD representing the month and day components of the date, respectively (2 digits with leading zero). TIME values are typically formatted as HH:MM:SS, with HH, MM, and SS representing the hours, minutes, and seconds components of the time, respectively.

Example 1: To creating a table column with date is as follows

CREATE TABLE TestDate ( Col1 date )

Inserting Date Values

Insert into TestDate (col1) values ( '2020-12-15')

select * from TestDate

col1
----------
2020-12-15

Example 2: The following statement demonstrates how to insert a date value in database table:

INSERT INTO employees (emp_name, hire_date, salary)
VALUES ('Adam Smith', '2015-06-24', 4500);

Note: Because MySQL considers TIME values as elapsed time, the hours part of the TIME values may be bigger in MySQL. As a result, the TIME data type can be used to represent not only a time of day (which must be fewer than 24 hours), but also a time gap between two occurrences, which can be higher than 24 hours or even negative.

Example 1: Format for Inserting the date:

You can show the date in any manner you choose, but you must be careful while adding it into the database.

This is because, depending on where you reside, the date 02/03/2020 might be read as 2nd March or 3rd February.

1. YYYYMMDD Universal format

When entering a date into a database, always use the format YYYYMMDD. SQL Server uses this as its default format. It's also the safest format because it can only be interpreted one way.

//YYYY-MM-DD//
Insert into TestDate (col1) values ( '2020-12-15')
Insert into TestDate (col1) values ( '2020.12.15')
Insert into TestDate (col1) values ( '2020/12/15')
Insert into TestDate (col1) values ( '20201215')
Insert into TestDate (col1) values ( '2020-5-2')

Regardless of the SQL Server's language or DATEFORMAT, the following query will run without errors.

2. The format may one of dmy, mdy & ymd.

If the format is dmy, the value 12-15-2020 can be used. If the format is mdy, however, an error will occur.

Using DATEFORMAT

DATEFORMAT can also be used to set the format to dmy, mdy, or ymd. Only the current session will benefit from the setting. The DATEFORMAT reverts to the default when a new session begins.

SET DATEFORMAT 'mdy'
 
Insert into TestDate (col1) values ( '12-15-2020')
Insert into TestDate (col1) values ( '12/15/2020')
Insert into TestDate (col1) values ( '12.15.2020')
Insert into TestDate (col1) values ( '5.2.2020')

3. Two digit year

Four-digit or two-digit years can be specified for the year portion. When possible, use four-digit years.

The cut-off year for two-digit years is 2049, and the period span is 1950 to 2049.

As a result, the years 1950 through 1999 are referred to as 1950-1999. The years 00 to 49 are referred to as 2000 to 2049.

Insert into TestDate (col1) values ( '70-12-01')
Insert into TestDate (col1) values ( '49-12-01')
Insert into TestDate (col1) values ( '50-12-01')
Insert into TestDate (col1) values ( '01-12-01')
 
select * from TestDate

col1
----------
1970-12-01
2049-12-01
1950-12-01
2001-12-01

Using the two-digit year cutoff option, you can adjust the cutoff year.

Separators

As separators, you can use dashes (/), hyphens (-), or periods (.).

Example 2:

You can reformat the existing date and time variables with the DATE FORMAT() functions if you desire a more informative and understandable date format in your result set.

The values of the birth date column of the users table will be formatted in a more readable way, such as 1987-01-14 to January 14, 1987, using the SQL statement below.

SELECT name, DATE_FORMAT(birth_date, '%M %e, %Y') FROM users;

To disable a primary key in a SQL Server database, use the ALTER INDEX statement.

If the data has previously been cleansed and validated, it is advisable to eliminate constraints like PRIMARY KEY, UNIQUE KEY, and CHECK, as well as indexes, to enhance loading performance, particularly when a data warehouse or a huge table is loaded.

Remember that when you designate one of your table columns as the primary key, a Clustered Index is created for that column. It produces a Non-Clustered Index if the Clustered Index has already been built (before the primary key).

Because the main structure is disabled, if the primary key is generated with a Clustered Index and you disable it, the table will not be able to be accessed. You can disable the main key and still deal with it if it was created with a non-clustered index.

Syntax:

ALTER INDEX constraint_name ON table_name  
DISABLE;

Example 1:

USE tempdb;  
GO  
  
-- create a table with a primary key which is clustered  
CREATE TABLE dbo.Customer  
(  
 CustomerID int identity(1,1) not null  
 , FirstName varchar(100) not null  
 , Constraint PK_Customer PRIMARY KEY CLUSTERED (CustomerID)  
);  
  
-- create a table with a primary key which is nonclustered  
CREATE TABLE dbo.Employee  
(  
 EmployeeID int identity(1,1) not null  
 , FirstName varchar(100) not null  
 , Constraint PK_Employee PRIMARY KEY NONCLUSTERED (EmployeeID)  
);  
  
-- Insert some records  
INSERT INTO dbo.Customer  
 (FirstName) VALUES ('Dinesh'), ('Yeshan');  
INSERT INTO dbo.Employee  
 (FirstName) VALUES ('Jane'), ('Jack');  
  
-- checking records  
-- This should return all recorded we inserted  
SELECT * FROM dbo.Customer;  
SELECT * FROM dbo.Employee;  
  
-- Disable the primary key of the Customer  
-- by disabling associated index  
ALTER INDEX PK_Customer ON dbo.Customer DISABLE;  
  
-- Now following statements are not possible  
INSERT INTO dbo.Customer  
 (FirstName) VALUES ('Kate'), ('Mihen');  
SELECT * FROM dbo.Customer;  
  
-- Disable the primary key of the Employee  
-- by disabling the associated index  
ALTER INDEX PK_Employee ON dbo.Employee DISABLE;  
  
-- These statements work without any issue  
INSERT INTO dbo.Employee  
 (FirstName) VALUES ('Nazir'), ('Daniel');  
SELECT * FROM dbo.Employee;  
  
-- Enabling both  
ALTER INDEX PK_Customer ON dbo.Customer REBUILD;  
ALTER INDEX PK_Employee ON dbo.Employee REBUILD;  

Same goes to Unique Constraint as well.

This is conceivable with a data warehouse since, as a best practise, we do not make the column labelled as primary key as the clustered key (always, there are exceptions). For example, we may construct a primary key with all foreign keys columns, but the clustered index would be created with the DataKey column.

Example 2: Disable the primary key "customer_id" on the table "customers".

ALTER INDEX cricketers2_pk ON [javatpoint].[dbo].[cricketers2]  
DISABLE;

You must confirm that data is in a valid JSON format before inserting it into a JSON column.

Example 1: Here’s the same example, except this time we insert the JSON into a table that already exists.

Therefore, the first thing we need to do is create the table:

CREATE TABLE [dbo].[JsonCats2](
	[CatId] [int] NULL,
	[CatName] [varchar](60) NULL,
	[Sex] [varchar](6) NULL,
	[Cats] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now that we've done that, we can go ahead and populate that table with the contents of our JSON document.

Like this:

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "name" : "Fetch", "sex" : "Male" },
            { "name" : "Fluffy", "sex" : "Male" },
            { "name" : "Wag", "sex" : "Female" }
        ]
    }
}';

INSERT INTO JsonCats2

SELECT * 
FROM OPENJSON(@json, '$.pets.cats')
WITH  (
        CatId     int             '$.id',  
        CatName   varchar(60)     '$.name', 
        Sex       varchar(6)      '$.sex', 
        Cats      nvarchar(max)   '$' AS JSON   
    );

The only difference between this and the previous example is that I swapped out the following:

SELECT * INTO JsonCats1

With this:

INSERT INTO JsonCats2

SELECT * 

As a result, selecting the table's contents yields the same effect as the previous example.

SELECT * FROM JsonCats2;

Result:

+---------+-----------+--------+------------------------------------------------------+
| CatId   | CatName   | Sex    | Cats                                                 |
|---------+-----------+--------+------------------------------------------------------|
| 1       | Fluffy    | Female | { "id" : 1, "name" : "Fluffy", "sex" : "Female" }    |
| 2       | Long Tail | Female | { "id" : 2, "name" : "Long Tail", "sex" : "Female" } |
| 3       | Scratch   | Male   | { "id" : 3, "name" : "Scratch", "sex" : "Male" }     |
+---------+-----------+--------+------------------------------------------------------+

Example 2: The example creates a table with a JSON column, allocates and initializes a JSON instance using the JSON constructor, then inserts the JSON and integer values into the table.

CREATE TABLE my_table (eno INTEGER, edata JSON(100));

INSERT INTO my_table VALUES(1, 
	NEW JSON('{"name" : "Cameron", "age" : 24}')); 

The example inserts a JSON string into a table that contains a JSON column.

INSERT INTO my_table VALUES(2, 
	'{"name" : "Cameron", "age" : 24}');

If the string is not formatted correctly, an error will be reported.

INSERT INTO my_table VALUES(3,
	'{"name" : "Cameron"');

Example 3: INSERT...SELECT Statement

The example creates two tables, then inserts JSON data into the second table from the first table.

CREATE TABLE my_table (eno INTEGER, edata JSON(100));
CREATE TABLE my_table2 (eno INTEGER, edata JSON(20));

INSERT INTO my_table VALUES(1, 
	NEW JSON('{"name" : "Cam"}'));
INSERT INTO my_Table2
	SELECT * FROM my_table;
If the JSON data is too large to fit in the column an error is reported.
INSERT INTO my_table VALUES(1, 
	NEW JSON('{"name" : "Cameron", "age" : 24}'));
INSERT INTO my_Table2
	SELECT * FROM my_table;

Example 3: The following INSERT statement inserts a new row into the orders table.

INSERT INTO orders (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}');

It means John Doe bought 6 bottle of beers.

The following statement inserts multiple rows at the same time.

INSERT INTO orders (info)
VALUES('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
      ('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
      ('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');

The data values are input in a single row using the standard SQL INSERT query. This query fails if we try to input data into numerous rows at once.

Using a single SQL Server INSERT statement, you can insert numerous rows into a table.

As a result, we must use the SQL INSERT query in such a way that the input data is injected into numerous rows in order to save time during execution. In this method, we must run the insert query as many times as we want to input data into the table's rows.

When utilising this form of the INSERT statement, the maximum number of rows you can insert at once is 1,000. If you need to insert additional rows, you'll need to use numerous INSERT statements, BULK INSERT, or a derived table.

Syntax:

To add multiple rows to a table at once, you use the following form of the INSERT statement:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

In this syntax, instead of using a single list of values, you use multiple comma-separated lists of values for insertion.

Example 1: SQL Server INSERT multiple rows – examples:

We will use the sales.promotions table created in the previous tutorial for the demonstration.

If you have not yet created the sales.promotions table, you can use the following CREATE TABLE statement:

CREATE TABLE sales.promotions (
    promotion_id INT PRIMARY KEY IDENTITY (1, 1),
    promotion_name VARCHAR (255) NOT NULL,
    discount NUMERIC (3, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
); 

1) Inserting multiple rows example

The following statement inserts multiple rows to the sales.promotions table:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
    (
        '2019 Summer Promotion',
        0.15,
        '20190601',
        '20190901'
    ),
    (
        '2019 Fall Promotion',
        0.20,
        '20191001',
        '20191101'
    ),
    (
        '2019 Winter Promotion',
        0.25,
        '20191201',
        '20200101'
    );

The SQL server returned the following response, indicating that three rows had been successfully added.

Let’s verify the insert by executing the following query:

SELECT *
FROM sales.promotions;

2) Inserting multiple rows and returning the inserted id list example

This example inserts three rows into the sales.promotions table and returns the promotion identity list:

INSERT INTO 
	sales.promotions ( 
		promotion_name, discount, start_date, expired_date
	)
OUTPUT inserted.promotion_id
VALUES
	('2020 Summer Promotion',0.25,'20200601','20200901'),
	('2020 Fall Promotion',0.10,'20201001','20201101'),
	('2020 Winter Promotion', 0.25,'20201201','20210101');

You learnt how to utilize another form of the SQL Server INSERT command to insert numerous rows into a table with just one INSERT statement in this example.

Example 2:

create table Info(id integer, Cost integer, city varchar(200));

insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

select * from Info;

Output:

1   100 Pune
2   50  Satara
3   65  Pune
4   97  Mumbai
5   12  USA

INSERT-SELECT-UNION query to insert multiple records:

We learned in the last section that the INSERT INTO query injects several records. However, if we look at the output, we can see that the sentence 'INSERT INTO' appears several times.

As a result, we can put data into many rows of the table using the INSERT-SELECT-UNION query.

Through the INSERT statement, the SQL UNION query helps to select all of the data that has been encompassed by the SELECT query.

create table Info(id integer, Cost integer);
INSERT INTO Info (id, Cost)  
SELECT 1, '123' 
UNION ALL  
SELECT 2, '234' 
UNION ALL 
SELECT 3, '456';  
 
select * from Info;

Output:

1   123
2   234
3   456

Example 3:

create table Info(id integer, Cost integer,city nvarchar(200));
INSERT INTO Info (id,Cost,city)  
VALUES (1,200, 'Pune'), (2, 150,'USA'), (3,345, 'France');  
 
select * from Info;

Output:

1   200 Pune
2   150 USA
3   345 France

Example 4: Insert multiple rows using INSERT:

Let us say you have the following table employees(id, first_name, last_name).

create table employees(id int,
first_name varchar(255),
last_name varchar(255));

You must specify the table name into which you want to insert values in the above query. Then, in a comma-separated fashion, input the values of each row enclosed in round brackets '()'.

The SQL statement to insert numerous rows of data into the employees table is shown below.

insert into employees(id, first_name, last_name)
values(1,'John','Doe'),
(2,'Jane','Doe');

select * from employees;

+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

Example 5: Insert Multiple Rows Without Duplicate:

Use the IGNORE keyword after INSERT in your SQL query to automatically avoid duplicate records when inserting multiple values in your table.

This, however, only works for tables with a primary key.

create table employees(id int primary key,
first_name varchar(255),
last_name varchar(255));

insert ignore into employees(id, first_name, last_name)
values(1,'John','Doe'),
(1,'John','Doe');

select * from employees;

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Doe       |
+----+------------+-----------+

Insert Multiple Rows in SQL using While Loop

As a result, we can insert many rows using another table. As a result, if we need to use for several tables, we need use a full join table of SQL to insert numerous data from another table.

Most importantly, understand table joins, including what they are and how to use them in a MySQL table.

So, if you want to learn more about table joins, go to Relational Algebra and look over all the examples and statements for inserting numerous rows from another example.

After that, you'll put the above principle into practise by using the syntax statement as an example. In the sections below, we'll show you how to insert many rows in a different approach.

WHILE ( conditions ){

// SQL Statement

}

Finally, in the above example, we defined a while loop, and there are show conditions in between the SQL statements for many rows. So, in the while loop example, I'll show you how to insert many rows in SQL.

$a = 1;
WHILE (  $a < 10){
INSERT INTO Demo ( id, name ) VALUES ( '1', 'Rohan' );
}

As a result, we must demonstrate a two-column insert statement in this example. As a result, the insert value to 10 times in the table's rows because the condition mentions 10 instead of 10.

As a result, the result will be displayed in the table with the same value records. Now, in order to get the desired outcome, you must first create the condition and then use the dynamic insert statement.

In addition, we are employing a recurrent loop. There are many examples of inserting many rows in SQL using the loop.

DISTINCT can be used in conjunction with the INSERT INTO SELECT statement. You can use a Choose query to select values from another table and feed them to the INSERT statement instead of giving a list of values. You can use this to replicate data from one table to another.

You may only need to insert a few rows from another table into a table on occasion. In this situation, you utilise criteria in the WHERE clause to limit the number of rows returned by the query.

The SELECT clause's list of columns must correspond to the INSERT INTO clause's list of columns. If you simply want to copy some of the data, use the WHERE clause to set a condition.

Example 1:

Step 1: Let us first create a table :

create table DemoTable1
(
   Value int
);

Step 2: Insert some records in the table using insert command :

insert into DemoTable1 values(50);
insert into DemoTable1 values(10);
insert into DemoTable1 values(10);
insert into DemoTable1 values(60);
insert into DemoTable1 values(50);
insert into DemoTable1 values(70);
insert into DemoTable1 values(50);

Step 3: Display all records from the table using select statement :

select *from DemoTable1;

Output :

+-------+
| Value |
+-------+
|    50 |
|    10 |
|    10 |
|    60 |
|    50 |
|    70 |
|    50 |
+-------+

Following is the query to create the second table.

create table DemoTable2
(
   Marks int
);

The query to insert many rows from another table is as follows. The records that are inserted should be distinct:

insert into DemoTable2(Marks) select distinct Value from DemoTable1;

Display all records from the table using select statement :

select *from DemoTable2;

Output:

+-------+
| Marks |
+-------+
|    50 |
|    10 |
|    60 |
|    70 |
+-------+

Example 2: The following line updates the addresses table with the addresses of the stores in Santa Cruz and Baldwin:

INSERT INTO 
    sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.stores
WHERE
    city IN ('Santa Cruz', 'Baldwin')

The following message was given by SQL Server, showing that two rows were successfully inserted.

Example 3: The following INSERT statement inserts two rows into the shippers table:

INSERT INTO shippers(companyName,phone)
VALUES ('UPS','1-800-782-7892'),
       ('DHL','1-800-225-5345')

SQL INSERT statement – copy table data

Suppose you have a temporary table named shippers_tmp that has the same structure as the shippers table. To copy data from the shippers table to the shippers_tmp table, you use the following statement:

INSERT INTO shippers_tmp (shipperid,name,phone)
SELECT 
    shipperid, companyName, phone
FROM 
    shippers

you have learned how to use the INSERT statement to insert one or more rows into a table.

A NULL value for a column can also be inserted using the SQL INSERT statement. A NULL value is not the same as a zero value.

In SQL, we sometimes need to enter rows containing NULL values into tables due to a lack of data. The keyword NULL (without quotes) is used to denote the absence of data. The following are some of the most important aspects of Null value:

A NULL value is used to denote a missing value, however it can be interpreted in one of three ways:

• The value is unknown (value exists but is not known).
• There is no value available (exists but is purposely withheld).
• Not relevant attribute (undefined for this tuple).
• It is frequently impossible to tell which of the several meanings is meant. As a result, SQL does not distinguish between the many NULL interpretations.

Syntax:

INSERT INTO TABLE_NAME values
(COLUMN_VALUE,NULL,........);

Example 1:

Step 1: Create a Database. For this use the below command to create a database named Nullvalue.

CREATE DATABASE Nullvalue

Step 2: Use the Nullvalue database. For this use the below command.

USE Nullvalue

Step 3: Create a table WORKER inside the database Nullvalue. This table has 4 columns namely STUDENT_ID, STUDENT_NAME, STUDENT_STANDARD, ENGLISH, MATHS, and PHYSICS containing student id, student name, standard, and marks of various subjects.

CREATE TABLE WORKER(
W_NAME VARCHAR(20),
CITY VARCHAR(20),
AGE INT);

Step 4: Display the structure of the WORKER table.

EXEC SP_COLUMNS 'WORKER';

Step 5: Insert 10 rows into the WORKER table.

INSERT INTO WORKER VALUES('SAM','ONTARIO',NULL);
INSERT INTO WORKER VALUES('TIM',NULL,56);
INSERT INTO WORKER VALUES(NULL,'CAIRO',43);
INSERT INTO WORKER VALUES(NULL,'MUMBAI',NULL);
INSERT INTO WORKER VALUES(NULL,NULL,NULL);

Step 6: Display all the rows of the WORKER table including the 0(zero) values.

SELECT * FROM WORKER;

Thus, in this way, we can insert NULL values into a table.

Example 2:

Sample table: agents

To add values'A001','Jodi', and ', 12' against the columns 'agent_code', 'agent_name' and 'commission' into the table 'agents', the following SQL statement can be used:

INSERT INTO agents
(agent_code,agent_name,commission)
VALUES ("A001","Jodi",.12)

Insert with Single Quotes in String

Is it a common problem to put a string value with an apostrophe (single quotation) in a column? It usually happens when you use an apostrophe in a name. This was a problem for one of my coworkers this morning. He needed to submit a list of clients, and some of the names contained an apostrophe, causing an issue during inclusion. He emailed me after a while, saying he had corrected it. So I asked him how he accomplished it, and he gave me an intriguing response: he copied the name, then opened the table and pasted it ;). Opsssssssss. This procedure should never be used.

Lets now resolve it step by step.

Step 1: Create a sample table.

USE tempdb
GO
CREATE TABLE tbl_sample
(
  [ID] INT,
  [Name] VARCHAR(50)
)
GO

Step 2: Insert the name with apostrophe. This step is just to demonstrate the error.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,'Irwin D'Mello')
GO

Output:

Step 3: Just replace the single apostrophe with double apostrophe and insert the record again.

USE tempdb
GO
INSERT INTO tbl_sample VALUES (1,'Irwin D''Mello')
GO

Step 4: Lets check if the data is inserted or not.

USE tempdb
GO
SELECT * FROM tbl_sample
GO

You can now see the name in the right format.

Conclusion:

Remember, whenever you come across such cases, just replace apostrophe (single quote) with double apostrophe (double quotes) and it works fine.

Example 2:

In SQL Server, you can simply escape a single quote by doubling it. Yes, it's that easy. SQL Server will save the single quotation along the string in the table if you use two quotes (") instead of one.

I actually came across a circumstance where I needed to input a string like Exclusive Men's Wear into a textbox and store the information as is in a SQL Server table while working on an e-commerce project. Examine the string value carefully now; the value Men's has a single quotation.

The INSERT statement, after the app picks up the values from the textbox looked like this.

INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men's Wear')

Because there is no closing bracket after the first single quotation (i.e., after the letter n in Men's), SQL Server will throw an error. Despite the fact that this is a pretty common problem with which I have dealt many times before, I made a mistake. The error was not doubling the amount.

The above statement should be,

INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men''s Wear')

See, now the string Men''s has two single quotes. This is fine. SQL Server will execute the statement and will insert the values in the table.

Remember, it is not a double quote, but two single quotes.

You must use the " ' " escape character to insert a special character into MySQL, such as "'" (single quote).

Syntax:

insert into yourTableName(yourColumnName) values(' yourValue\’s ');

Example 1: let us create two tables. The query to create first table is as follows :

mysql> create table AvoidInsertErrorDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Sentence text
-> );

Now you can insert special character such as ‘ in the table using insert command. The query is as follows :

mysql> insert into AvoidInsertErrorDemo(Sentence) values('a woman\'s hat');
mysql> insert into AvoidInsertErrorDemo(Sentence) values('Mrs. Chang\'s house');

Display all records from the table using select statement:

mysql> select *from AvoidInsertErrorDemo;

Output :

+----+--------------------+
| Id | Sentence           |
+----+--------------------+
|  1 | a woman's hat      |
|  2 | Mrs. Chang's house |
+----+--------------------+

Example 2:

Ampersand:

SET DEFINE OFF disables SQL+'s special meaning for the & symbol, which converts a word into a variable.

SET DEFINE OFF
UPDATE EO
SET DIRECTIONS = 'CORNER OF 16TH ST NW & I ST NW'
where eo_id = 1;
SET DEFINE ON

Semicolon:

SET SQLTERMINATOR OFF is supposed to remove the special meaning of ;, but it doesn't seem to work. Instead, change the SQL Terminator to another symbol, e.g. "~":

SET SQLTERMINATOR ~ (or use SET SQLT ~ for short)

To turn semicolon back on use:

>SET SQLTERMINATOR ON

Apostrophe (=Single Quote):

In your insert statements, replace all apostrophes with two apostrophes; they'll be added to the database as one.

UPDATE EO
SET DIRECTIONS = 'TODD''S FORK'
where eo_id = 1;

Following is an Excel macro that encloses text (in each selected cell) in single quotes, replaces ' with '', and trims outside spaces, to prepare text for SQL insert statements.

Give the column names in parenthesis to insert data into specified columns. If you don't accept null values in other columns, you'll get an error.

There are times when you only need to insert values into specific table columns.

Syntax:

Insert Into TABLE_NAME Values(value1, value2, value3, value4...)

OR

Insert into TABLE_NAME (Column_Name, Column_Name2, Column_Name3, Column_Name4...) 
Values (Value1, Value2,Value3, Value4...)

Description:

• Insert Into: Insert Into is a sql statement which is sued to insert value into table.
• TABLE_NAME: It is user define name of table. Like StudentDetails, Employee etc.
• Values: Value is a sql statement which is used with Insert Into in sql statement.

Example 1: The following will insert data in FirstName, and LastName columns only.

Insert Data to Specific Columns :

INSERT INTO Employee(FirstName, LastName)
VALUES('James','Bond');

Note: You must fill in all NOT NULL fields with data; else, an error will occur.

Example 2: Table in Sql Server (Note: If table has not created then create table before:

Create Table ItemDetails
(
ItemCode int,
ItemName varchar(30),
ManufactureCompany varchar(50),
ItemSearialNo varchar(30),
ItemCategory varchar(30),
ItemQuantity int,
ItemPrice float,
ProductCountry varchar(30)
)

Insert into ItemDetails values('1','Samsung Mobile','Samsung','S12345','Mobile','500','240','Korea')
Insert into ItemDetails values('2','Samsung Pro','Samsung','SP1234','Mobile','100','300','Korea')
Insert into ItemDetails values('3','Samsung Headphone','Samsung','SH2345','Headphone','50','50','Korea')
Insert into ItemDetails values('4','iPhone','iPone','iP12345','Mobile','1000','500','United Stat')
Insert into ItemDetails values('5','Apple Mobile','Apple','A12345','Mobile','200','300','United Stat')
Insert into ItemDetails values('6','Apple Computer','Apple','A12343','Computer','200','400','United Stat')
Insert into ItemDetails values('7','Apple Macbook','Apple','AM2345','Macbook','50','600','United Stat')
Insert into ItemDetails values('8','Dell Computer','DELL','D02345','Computer','200','240','United Stat')
Insert into ItemDetails values('9','Dell Laptop','DELL','DL2345','Laptop','30','240','United Stat')
Insert into ItemDetails values('10','Apple Laptop','Apple','AL2345','Laptop','500','100','United Stat')

Second Method

Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,
ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry) 
values('17','Ascer Printer','Ascer','AP2345','Printer','200','150','China')

Insert into ItemDetails (ItemCode,ItemName,ManufactureCompany,
ItemSearialNo,ItemCategory,ItemQuantity,ItemPrice,ProductCountry) 
values('18','Brother Photocopy','Brother','BP2345','Printer','20','150','China')

To construct a stored procedure in SQL Server that inserts a row into a table.

Variables are utilized with stored procedures to insert records into a table in most applications.

Example 1: Let's start by making a table. I'll make a table with the names of the states of the United States of America, as well as their populations.

USE BackupDatabase
GO
CREATE TABLE dbo.States_in_USA(
	[Sr. No] int IDENTITY(1,1),
	[State] nvarchar(30),
	[Population] int
)

Now we will create a stored procedure using which we can insert the data into the table.

USE BackupDatabase
GO
CREATE PROCEDURE dbo.InsertInto @state nvarchar(30), @population int
AS
BEGIN
	INSERT INTO dbo.States_in_USA(
		State, Population)
		VALUES(@state, @population)
END

We have defined two parameters in the stored procedure:

• @state: Name of a state in USA
• @population: Population of the provided state

These values will be passed to the stored procedure, which will use them in an insert statement to place the record into the dbo.States_in_USA table.

Example 2: SQL Server stored procedure insert into table return id:

The SCOPE IDENTITY() method returns the last added identity value from a table. The SCOPE IDENTITY function can be used to get the latest inserted identity column value when inserting a row in a table.

In this part, we'll develop an instance of a stored procedure that inserts a record into a table that has an identity column. It will also return the last entered identification column through an output parameter after inserting the record.

Consider the table TableIdentity.

Let's make a stored procedure that inserts a row into the table and returns the value of the last added identity column.

USE master
GO
CREATE PROCEDURE dbo.ReturnID @EmpName nchar(20), @EmpDep nchar(10),
	@Identity int OUTPUT
AS
BEGIN
	INSERT INTO dbo.TableIdentity(EmpName, EmpDep)
	VALUES( @EmpName, @EmpDep)
	SET @Identity= SCOPE_IDENTITY()
END

Example 3: SQL Server stored procedure insert into multiple tables:

• Persons: Storing personal details of people
• Phones: Storing only the phone numbers and names of the people in the Persons table.

We will create a stored procedure that will insert the records in both th tables at a single execution.

USE master
GO
CREATE PROCEDURE dbo.InsertMultiple @FirstName nchar(10),
	@LastName nchar(10), @Age smallint, @Gender nchar(7),
	@Email nchar(30), @Phone nchar(20), @Location nchar(20)
AS
BEGIN
	INSERT INTO dbo.Persons(
	[First Name], [Last Name], Age, Gender, Email, Phone, Location)
	VALUES(@FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
	INSERT INTO dbo.Phones(
	[Phone], [First Name], [Last Name])
	VALUES( @Phone, @FirstName, @LastName)
END

The values we want to enter into the tables will be passed to the stored method as input parameters.

Example 4: Insert Stored Procedure in SQL Server Example:

Within the Stored procedure, use the INSERT statement. To learn the fundamentals of SP, I recommend reading the essay Introduction to SP.

IF OBJECT_ID ( 'InsertStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFirstExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFirstExample

AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	VALUES ('Tutorial', 'Gateway', 'Education', 10000, 200)
          ,('Imran', 'Khan', 'Skilled Professional', 15900, 100)
          ,('Doe', 'Lara', 'Management', 15000, 60)
          ,('Ramesh', 'Kumar', 'Professional', 65000, 630)

END
GO

We're entering four entries into our empty EmployeeDup table, as you can see from the code sample above. Execute the sp listed above.

Output:

During a recent training session for one of my clients, I was questioned about the TOP clause improvement. When I displayed my script for how TOP and INSERT work together, one of the attendees suggested that I write about it on my blog as well.

It's worth noting that there are two methods for limiting the number of rows that can be inserted into the table.

Method 1:

INSERT INTO TABLE …
SELECT TOP (N) Cols…
FROM Table1

Method 2:

INSERT TOP(N) INTO TABLE …
SELECT Cols…
FROM Table1

Example 1:

The second method which in fact is the enhancement in TOP along with INSERT.

Let us play with one real example and we understand what exactly is happening in either of the case.

USE tempdb
GO

Create Table

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TestValue') AND type IN (N'U'))
DROP TABLE TestValue
GO
CREATE TABLE TestValue(ID INT)
INSERT INTO TestValue (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
GO

Select Data from Table:

SELECT *
FROM TestValue
GO

Create Two Table where Data will be Inserted:

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue') AND type IN (N'U'))
DROP TABLE InsertTestValue
GO
CREATE TABLE InsertTestValue (ID INT)
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue1') AND type IN (N'U'))
DROP TABLE InsertTestValue1
GO
CREATE TABLE InsertTestValue1 (ID INT)
GO

Option 1: Top with Select

INSERT INTO InsertTestValue (ID)
SELECT TOP (2) ID
FROM TestValue
ORDER BY ID DESC
GO

Option 2: Top with Insert

INSERT TOP (2) INTO InsertTestValue1 (ID)
SELECT ID
FROM TestValue
ORDER BY ID DESC
GO

Check the Data

SELECT *
FROM InsertTestValue
GO

SELECT *
FROM InsertTestValue1
GO

Clean up

DROP TABLE InsertTestValue
DROP TABLE InsertTestValue1
DROP TABLE TestValue
GO

Now let us check the result of above SELECT statements.

It is very interesting to see when Option 2 is used, ORDER BY is absolutely ignored and data is inserted in any order.

Example 2: INSERT creates the table Employee and adds the top 5 random employees' names and year-to-date sales data from the table EmployeeDetails into it. EmployeeId is a unique identifier in the Sample database. The INSERT statement selects up to five rows from the SELECT query. The rows that are inserted into the EmployeeDetails table are shown using the OUTPUT clause. The top 5 employees are not determined using the ORDER BY clause in the SELECT statement.

CREATE TABLE  Employee     
( EmployeeID   nvarchar(11) NOT NULL,      
  LastName     nvarchar(20) NOT NULL,      
  FirstName    nvarchar(20) NOT NULL,      
  YearlySales  money NOT NULL      
 );      
GO      

INSERT TOP(5)INTO  employeeDetails  
    OUTPUT inserted.EmployeeID, inserted.FirstName,     
        inserted.LastName, inserted.YearlySales      
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD       
    FROM Employee     AS sp      
    INNER JOIN Person.Person AS c      
        ON sp.BusinessEntityID = c.BusinessEntityID      
    WHERE sp.SalesYTD > 250000.00      
    ORDER BY sp.SalesYTD DESC;   

A trigger is a series of SQL statements that are defined to do a certain action and can be executed in response to a specific event.

Tables can be used to generate triggers. On a table, several triggers can be created.

To build a trigger, we utilise the CREATE TRIGGER statement. The table name and trigger type (Before or After) are specified. This determines whether the trigger is set to fire before or after the event.

Syntax:

CREATE TRIGGER < Trigger Name>
ON < table name>
AFTER|BEFORE < event>
AS
BEGIN
< Code to be run when the trigger will be fired>
END

Example 1: SQL Server trigger after insert example:

Products Table

We will create a trigger on this Products table that will display a message on the output screen.

CREATE TRIGGER InsertProducts
ON dbo.Products
AFTER INSERT
AS
BEGIN
	PRINT('Record(s) inserted successfully')
END

Example 2: A table called Persons has been created. On this table, we'll write a trigger that will fire whenever a new record is inserted.

USE master
GO

CREATE TRIGGER InsertPersons
ON dbo.Persons
AFTER INSERT
AS
BEGIN
DECLARE
@FirstName nchar(30),
@LastName nchar(30),
@Location nchar(30)
SET NOCOUNT ON;

SELECT @FirstName= INSERTED.[First Name],
@LastName= INSERTED.[Last Name],
@Location= Location FROM INSERTED
PRINT(@FirstName+ @LastName+ @Location)
END

The INSERTED table stores the data which is inserted or updated using the Insert or Update statement.

Example 3: SQL Server trigger after insert with condition:

After the Insert statement, use a trigger with a conditional statement. We'll use a table to create a trigger that includes a condition.

We have a table called Persons and another called Names, for example. For the table Persons, we'll design a trigger that will activate after an Insert statement.

With a condition, the trigger will create a new record in the Names table. The condition will determine whether or not the record already exists in the Names table. It will insert a new row in the table if the record is not already in the table; else, it will not put a new row in the table.

Persons Table

Names Table

We will create a trigger on the Persons table:

USE [master]
GO

CREATE TRIGGER [dbo].[InsertSP]
ON [dbo].[Persons]
AFTER INSERT
AS
BEGIN
	DECLARE
		@FirstName nchar(10),
		@LastName nchar(10),
		@FullName nchar(30)
	SELECT @FirstName= INSERTED.[First Name],
	@LastName= INSERTED.[Last Name] FROM INSERTED
	SET @FullName= @FirstName+ @LastName

	IF EXISTS(SELECT * FROM dbo.Names WHERE [First Name]= @FirstName AND [Last Name]= @LastName)
	BEGIN
		PRINT('Record Already Exists in the Names Table')
	END
	ELSE
	BEGIN
		INSERT INTO dbo.Names([First Name], [Last Name], [Full Name])
		VALUES(@FirstName, @LastName, @FullName)
		PRINT('Record Inserted in the Names Table')
	END	
END

If the record does not already exist in the Names table, the above trigger will insert it. Instead, the warning 'Record Already Exists in the Names Table' will appear.

SQL Server does not support the BEFORE INSERT trigger type. Only two types of triggers are supported by SQL Server:

Example 4: SQL Server before insert example:

The BEFORE INSERT trigger type is not supported by SQL Server. SQL Server supports only two types of triggers:

AFTER

However, if you want the same functionality as the BEFORE INSERT Trigger, you can use the INSTEAD OF INSERT trigger. The INSTEAD OF INSERT trigger will prevent the insert statement from running and only the SQL code inside the trigger will learn.

Inside the trigger body, firstly, write the queries that you want to execute before the Insert statement as you would write in the BEFORE INSERT trigger. Then write the records that you want to insert in an Insert statement.

Suppose we have created an INSTEAD OF INSERT trigger on a table. If we try to insert a row in the table, the code inside the trigger body will run only. This means the statements will not be inserted.

We have created a table named SumTable, It has three columns:

• First Number
• Second Number
• Sum of Numbers

The column Sum of Numbers stores the sum of the two columns.

You can also try this example on your own end by copying the SQL code below:

USE master
GO
DROP TABLE IF EXISTS dbo.SumTable
CREATE TABLE dbo.Sumtable(
[First Number] real,
[Second Number] real,
[Sum of Numbers] real
)

We will put a record in each of the two columns, First Number and Second Number, after the table has been formed.

On this table, we'll write a trigger that calculates the total of two numbers and stores the result in the Sum of Numbers column.

USE master
GO
CREATE TRIGGER TriggerSumTable
ON dbo.SumTable
INSTEAD OF INSERT
AS
BEGIN
	
DECLARE
	@Fnum real,
	@Snum real,
	@Sum real
	SELECT @Fnum= INSERTED.[First Number],
   @Snum= INSERTED.[Second Number]
 FROM INSERTED
	SET @Sum= @Fnum+ @Snum
	INSERT INTO dbo.SumTable(
	[First Number], [Second Number], [Sum of Numbers])
	VALUES(@Fnum, @Snum, @Sum)
END

Now we will insert a record in the table as our requirement:

USE master
GO
INSERT INTO dbo.SumTable( [First Number], [Second Number])
VALUES(1000, 1500)

In the table, you'll notice that a row has been put alongside the sum.

As a result, you should be able to use the INSTEAD OF type of trigger with the INSERT statement.

Read Saving changes is not permitted in SQL Server

SQL Server trigger after insert copy row to another table

In this part, you'll learn how to use a trigger after the Insert statement in SQL Server 2019 to copy a row to another table. You could want to put a row in two tables that are comparable. On a table, you can create a trigger that inserts the identical row into another table.

Consider the following two tables:

• Customers: The original table on which we will create a trigger.
• NewCustomers: The table into which you want to copy the rows from the Customers table. This table will be identical to the Customers table in structure.

Example 5: SQL Server trigger after insert copy row to another table:

Customers Table

• The Customers table contains some records at first, but the NewCustomers table is blank.
Let's construct a trigger on the Customers table. When we insert entries in the Customers table after generating the trigger, the same row will be replicated into the NewCustomers table.

USE master
GO
CREATE TRIGGER TriggerCustomers
ON dbo.Customers
AFTER INSERT
AS
BEGIN
	DECLARE
		@CustomerID int,
		@Name nchar(20)
	SELECT @CustomerID= INSERTED.[Customer ID],
	@Name= INSERTED.[Customer Name]
	FROM INSERTED
	INSERT INTO dbo.NewCustomers(CustomerID, [Customer Name])
	VALUES(@CustomerID, @Name)
END

Now we will insert a record in the Customers table.

USE master
GO
INSERT INTO dbo.Customers([Customer ID], [Customer Name])
VALUES(1263, 'Christiano')

You can observe in the below image that the same row is also copied into the NewCustomers table.

NewCustomers Table

Hence, you might have understood more about the triggers and how you can use these.

Example 6: SQL Server trigger after insert get id:

When you insert a row in a table with an identity column, you don't have to fill in the value since in SQL Server 2019, the values are automatically assigned to that field.

Assume you're putting the same rows to another table using an insert trigger on a table with an identity column. If you want to get at the identity values, you'll need to look at the INSERTED table. The record you're going to insert is stored in this table.

Consider the following TableIdentity table.

The table has three columns:

• EmpName
• EmpDep
• EmpID

The EmpID column is an identity column in this table.

Suppose we want to create a new table with same structure:

USE master
GO
DROP TABLE IF EXISTS dbo.NewTableIdentity
CREATE TABLE dbo.NewTableIdentity(
[Employee Name] nchar(20),
[Employee Department] nchar(10),
[Employee ID] int
)

The new table is NewIdentityTable.

Initially, both the tables are empty.

Example 7: Insert Performance with AFTER INSERT Trigger in SQL Server:

Now we will define an AFTER INSERT trigger on the table (removing the DEFAULT clause), and measure the insert performance:

SQL Server:

IF OBJECT_ID('sales', 'U') IS NOT NULL
 DROP TABLE sales;
 
-- Table definition
CREATE TABLE sales
  (
  id INT PRIMARY KEY,
  created DATETIME
  );
  GO
 
  -- Define a trigger
  CREATE TRIGGER tr_sales ON sales
    AFTER INSERT
    AS
    BEGIN
      UPDATE sales
      SET created = GETDATE()
      FROM inserted
      WHERE sales.id = inserted.id;
    END
   GO

Using the same Transact-SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

Execution time	Insert rate
19 seconds	5263 rows per second

When compared to using the DEFAULT clause, the statement-level AFTER INSERT trigger lowered insert performance by 58 percent in SQL Server.

Example 8: Introduction to MySQL BEFORE INSERT triggers:

MySQL BEFORE INSERT triggers are automatically fired before an insert event occurs on the table.

Basic syntax of creating a MySQL BEFORE INSERT trigger:

CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH ROW
trigger_body;

In this syntax:

• In the CREATE TRIGGER clause, first specify the name of the trigger you want to create.
• Second, set the time to initiate the trigger using the BEFORE INSERT clause.
• After the ON keyword, indicate the name of the table with which the trigger is related.
• Finally, specify the trigger body, which includes one or more SQL statements that will be executed when the trigger is triggered.

If the trigger body contains many statements, you must use the BEGIN END block and alter the default delimiter.

DELIMITER $$

CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;

You can access and alter the NEW values in a BEFORE INSERT trigger. However, you won't be able to access the OLD values because they don't exist.

Returns the updated JSON string after changing the value of a property in a JSON string.

For the Brand and Product keys, there is a key-value pair. Assume you want to use JSON MODIFY to alter the product value in this JSON ().

There are two ways to modify JSON data in a SQL Server table:

• To update particular properties within JSON data, use the JSON MODIFY() method in a UPDATE statement.
• Substitute a standard UPDATE statement for the JSON data.

Syntax:

JSON_MODIFY ( expression , path , newValue )  

Arguments:

• expression - An expression. Typically the name of a variable or a column that contains JSON text.
• JSON_MODIFY returns an error if expression doesn't contain valid JSON.
• path - A JSON path expression that specifies the property to update.

path has the following syntax:

[append] [ lax | strict ] $.< json path>

• append: The new value shall be appended to the array indicated by < json path>, according to this optional modification.
• lax: This specifies that the property referred to by < json path> is not required to exist. JSON MODIFY tries to insert the new value on the supplied route if the property is not existing. If the property cannot be added on the path, the insertion may fail. If you don't provide either lax or strict, the default option is lax.
• strict: Specifies that the json path> property must be present in the JSON expression. JSON MODIFY throws an error if the property isn't existent.
• < json path>: The route to the property that needs to be updated. See JSON Path Expressions for further information (SQL Server).

You can use a variable as the path value in SQL Server 2017 (14.x) and Azure SQL Database.

JSON_MODIFY returns an error if the format of path isn't valid.

newValue

• The new value for the property specified by path.
• The new value must be a [n]varchar or text.
• In lax mode, JSON_MODIFY deletes the specified key if the new value is NULL.

If the value's type is NVARCHAR or VARCHAR, JSON_MODIFY escapes all special characters in the new value. If a text value is properly formed JSON created using FOR JSON, JSON_QUERY, or JSON_MODIFY, it is not escaped.

Return Value: Returns the updated value of expression as properly formatted JSON text.

Remarks: Based on a combination of modes and given values, the JSON MODIFY function allows you to either update the value of an existing property, insert a new key:value pair, or delete a key.

The following table compares the behavior of JSON_MODIFY in lax mode and in strict mode. For more info about the optional path mode specification (lax or strict), see JSON Path Expressions (SQL Server).

New value	Path exists	Lax mode				Strict mode
Not NULL	Yes		Update the existing value.		Update the existing value.
Not NULL	No		Try to create a new key:value pair on the specified path.
This may fail. For example, if you specify the path $.user.setting.theme, 
JSON_MODIFY does not insert the key theme if the $.user or $.user.settings objects do not exist, 
or if settings is an array or a scalar value.	Error - INVALID_PROPERTY

NULL	Yes	Delete the existing property.	Set the existing value to null.
NULL	No	No action. The first argument is returned as the result.	Error - INVALID_PROPERTY

In lax mode, JSON_MODIFY tries to create a new key:value pair, but in some cases it might fail.

Example 1: Basic operations

The following example shows basic operations that can be done with JSON text.

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')
PRINT @info

-- Insert surname  

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

PRINT @info

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

JSON

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

Example 2: Rename a key

The JSON_MODIFY method is used to rename a property in JSON text in the example below. You can start by inserting the value of an existing property as a new key:value pair. Then, by setting the value of the old property to NULL, you can delete the old key.

DECLARE @product NVARCHAR(100)='{"price":49.99}'

PRINT @product

-- Rename property  

SET @product=
 JSON_MODIFY(
  JSON_MODIFY(@product,'$.Price',CAST(JSON_VALUE(@product,'$.price') AS NUMERIC(4,2))),
  '$.price',
  NULL
 )

PRINT @product

Results:

JSON

{
    "price": 49.99
} {
    "Price": 49.99
}

If you don't cast the new value to a numeric type, JSON_MODIFY treats it as text and surrounds it with double quotes.

Example 3: Using JSON to populate a table. The rest of this author's sections will change this JSON data.

My first JSON data is shown below. I'm just using an ordinary INSERT statement to put it into a table:

DECLARE @playersJson nvarchar(max) = '{
  "QB1": {
    "Name": "Matthew Stafford",
    "YearsOfExperience": 12,
    "College": "Georgia"
  },
  "RB1": {
    "Name": "D''Andre Swift",
    "YearsOfExperience": 1,
    "College": "Georgia"
  },
  "WR1": {
    "Name": "Kenny Golladay",
    "YearsOfExperience": 4,
    "College": "Northern Illinois"
  },
  "TE1": {
    "Name": "T.J. Hockenson",
    "YearsOfExperience": 2,
    "College": "Iowa"
  }
}'

INSERT INTO NFLTeams
(TeamName, Players)
VALUES
('Detroit Lions',
@playersJson)

Example 1: Assume you have a table that is exclusively used for data inserts. Because the table lacks a primary key, inserts are quick, allowing us to establish a table without a primary key or clustered index. Getting rows from such a table with a SELECT, on the other hand, will be extremely sluggish unless the table has other indexes. It's fine without a primary key or a clustered index as long as the table is only used for inserts).

Backup Tables

SELECT * INTO is used to construct a backup table. These are frequently constructed as temporary tables to store data, although they are occasionally exploited beyond their intended purpose.

Let's establish a very simple Sale table with the following T-SQL code to gain a better understanding of how it works:

CREATE TABLE Sale (
  SaleId INT IDENTITY
 ,ProductId INT NULL
 ,SaleDate DATETIME2 NULL
 ,SaleAmount DECIMAL(10, 2) NULL
 ,CONSTRAINT PK_Sale_SaleId PRIMARY KEY CLUSTERED (SaleId)
)
GO

Insert data into the table as follows:

SET IDENTITY_INSERT [dbo].[Sale] ON
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], 
[SaleAmount]) VALUES (1, 1, N'2017-10-02 21:35:55', CAST(300.50 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], 
[SaleAmount]) VALUES (2, 1, N'2017-10-11 21:36:30', CAST(400.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], 
[SaleAmount]) VALUES (3, 2, N'2017-10-13 21:36:55', CAST(350.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], 
[SaleAmount]) VALUES (4, 3, N'2017-10-16 21:37:13', CAST(500.50 AS Decimal(10, 2)))
INSERT INTO [dbo].[Sale] ([SaleId], [ProductId], [SaleDate], 
[SaleAmount]) VALUES (5, 4, N'2017-11-14 21:37:32', CAST(460.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[Sale] OFF

Viewing Sale table

SELECT  s.SaleId
       ,s.ProductId
       ,s.SaleDate
       ,s.SaleAmount  FROM Sale s

Now create a copy of the table named SaleBackup using the following script:

Creating backup table SaleBackup from Sale table

SELECT * INTO SaleBackup From Sale

Retrieve data from the table as follows:

View SaleBackup table created from Sale table

SELECT SaleId
      ,ProductId
      ,SaleDate
      ,SaleAmount FROM SaleBackup

When you look at the design of the backup table SaleBackup, you'll note that it doesn't have a key, despite the fact that it contains the same data as the original Sale table, which did.

Staging tables, which are utilised in data warehouses, business intelligence systems, and other data migration projects, are another example of tables without primary keys. Staging tables, as the name implies, are used to store data from live data sources so that subsequent data processing does not rely on the production database. It is possible to build staging tables without a key restriction, however this is not always the case.

Example 2: Insert without primary key:

INSERT INTO prod_mast(prod_name, prod_rate, prod_qc)
VALUES('Gulha', 55, 'Problems');

The above example shows that the prod_id column not been included within the column list.

As the prod_id column is defined as INTEGER PRIMARY KEY, so it is auto-incremented by SQLite. So it is clear that the SQLite library adds a new id.

Here is the inserted row.

SELECT * FROM prod_mast;

Output:

prod_id          prod_name   prod_rate   prod_qc
---------------  ----------  ----------  ----------
1                Pancakes    75          OK
2                Gulha       55          Problems

Inserts one or more Expression1-identified nodes as children or siblings of the Expression2-identified node.

We use the XML data type as an input parameter to insert entries from XML data into a SQL Server database table.

Create a stored procedure with Xml as the input parameter type, as shown below.

Syntax

insert Expression1 (  
{as first | as last} into | after | before  
Expression2  
)  

Note: To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

Expression1

One or more nodes to insert are identified. This can be a constant XML instance, a reference to a typed XML data type instance from the same XML Schema collection as the alter method, an untyped XML data type instance created with the stand-alone sql:column()/sql:variable() function, or an XQuery expression. A node, as well as a text node, or an ordered sequence of nodes, might be the result of the expression. It is unable to reach the root (/) node. If the expression returns a value or a sequence of values, the values are entered as a single text node, separated by a space. When several nodes are specified as constants, they are enclosed in parenthesis and separated by commas. You can't put together heterogeneous sequences like elements, characteristics, or values. There is no insertion and no errors if Expression1 resolves to an empty sequence.

into

Expression1- identified nodes are inserted as direct descendants (child nodes) of Expression2-identified nodes. If the Expression2 node already has one or more child nodes, use either as first or as last to describe where the new node should be added. For instance, at the beginning or end of the child list, accordingly. When attributes are entered, the as first and as last keywords are disregarded.

after

Expression1-identified nodes are put as siblings right after Expression2-identified nodes. You can't use the after keyword to insert characteristics. It can't be used to create an attribute function Object() { [native code] } or return an attribute from an XQuery, for example.

before

Expression1-identified nodes are put as siblings directly before Expression2-identified nodes. When inserting attributes, the before keyword isn't allowed. It can't be used to create an attribute function Object() { [native code] } or return an attribute from an XQuery, for example.

Expression2-This property identifies a node. The nodes found in Expression1 are put next to the node found in Expression2. This might be an XQuery expression that returns a reference to a node in the document presently being referenced. The insert fails if more than one node is returned. There is no insertion and no problems if Expression2 returns an empty sequence. A static error is thrown if Expression2 is not a singleton statically. Expression2 isn't allowed to be a processing instruction, a comment, or a property. Expression2 must be a reference to a node that already exists in the document, not a newly created node.

Example 1: To create a test table with an Identity column, and a column of XML data type use the following code:

CREATE TABLE XML_Test_Table
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML
)

You can insert XML data directly into the table using the INSERT command:

insert_into_xml_directly

As you can see, XML data is inserted into the XMLData column:

SELECT * FROM XML_Test_Table

insert_xml_directly

Clicking on the hyperlink open a tab in SSMS with the XML data:

insert_xml_directly_data

Example 2:

CREATE PROCEDURE InsertPersonalDetailsFromXML
       -- Add the parameters for the stored procedure here
       @xmlData XML
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here

       INSERT INTO PersonalDetails
       (FirstName, LastName, Age, Active)

       SELECT x.value('FirstName[1]', 'varchar(50)') AS FirstName,
           x.value('LastName[1]', 'varchar(50)') AS LastName,
                 x.value('Age[1]', 'int') AS Age,
                 x.value('Active[1]', 'bit') AS Active
       FROM @xmlData.nodes('//PersonalDetail') XmlData(x)
END

Notice that the input parameter named @xmlData is of XML type. Next, we are using INSERT statement and selecting the value of each node of “PersonalDetail” to insert into PersonalDetails table.

To execute above stored procedure, call following script

DECLARE @xmlData Xml
SET @xmlData = '<PersonalDetails>
  <PersonalDetail>
    <FirstName>XmlFirstName 11</FirstName>
    <LastName>XmlLastName 1</LastName>
    <Age>30</Age>
    <Active>1</Active>
  </PersonalDetail>
  <PersonalDetail>
    <FirstName>XmlFirstName 22</FirstName>
    <LastName>XmlLastName 2</LastName>
    <Age>25</Age>
    <Active>1</Active>
  </PersonalDetail>
</PersonalDetails>'

EXEC InsertPersonalDetailsFromXML @xmlData

Note that the @xmlData is of Xml type, here even if we change the variable type to varchar it will work as its string value is a valid Xml.

Above code snippet inserts two records into PersonalDetails database table.

Example 3:

A. Inserting element nodes into the document

How to put elements into a document is demonstrated in the following example. An XML document is first assigned to an xml type variable. The example then shows how element nodes are put in the document using many insert XML DML statements. The SELECT statement displays the result after each insert.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;         
SET @myDoc = '<Root>         
    <ProductDescription ProductID="1" ProductName="Road Bike">         
        <Features>         
        </Features>         
    </ProductDescription>         
</Root>'  ;       
SELECT @myDoc;     

-- insert first feature child (no need to specify as first or as last)         
SET @myDoc.modify('         
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>   
into (/Root/ProductDescription/Features)[1]') ;  
SELECT @myDoc ;        

-- insert second feature. We want this to be the first in sequence so use 'as first'         
SET @myDoc.modify('         
insert <Warranty>1 year parts and labor</Warranty>          
as first         
into (/Root/ProductDescription/Features)[1]         
')  ;       
SELECT @myDoc  ;       

-- insert third feature child. This one is the last child of <Features> so use 'as last'         
SELECT @myDoc         
SET @myDoc.modify('         
insert <Material>Aluminium</Material>          
as last         
into (/Root/ProductDescription/Features)[1]         
')         
SELECT @myDoc ;        

-- Add fourth feature - this time as a sibling (and not a child)         
-- 'after' keyword is used (instead of as first or as last child)         
SELECT @myDoc  ;       
SET @myDoc.modify('         
insert <BikeFrame>Strong long lasting</BikeFrame>   
after (/Root/ProductDescription/Features/Material)[1]         
')  ;       
SELECT @myDoc;  
GO  

Note that various path expressions in this example specify "[1]" as a per-static typing requirement. This ensures a single target node.

B. Inserting multiple elements into the document

A document is first allocated to an xml type variable in the following example. Then, to a second xml type variable, a sequence of two components indicating product features is allocated. The first variable is then inserted with this sequence.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;  
SET @myDoc = N'<Root>             
<ProductDescription ProductID="1" ProductName="Road Bike">             
    <Features> </Features>             
</ProductDescription>             
</Root>';  
DECLARE @newFeatures xml;  
SET @newFeatures = N'<Warranty>1 year parts and labor</Warranty>            
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>';           

-- insert new features from specified variable            
SET @myDoc.modify('             
insert sql:variable("@newFeatures")             
into (/Root/ProductDescription/Features)[1] ')             
SELECT @myDoc;  
GO  

C. Inserting attributes into a document

The example below shows how to introduce attributes into a document. First, an xml type variable is assigned to a document. The document is then filled with attributes using a sequence of insert XML DML instructions. The outcome of each attribute insertion is displayed using the SELECT query.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;            
SET @myDoc =   
'<Root>             
    <Location LocationID="10" >             
        <step>Manufacturing step 1 at this work center</step>             
        <step>Manufacturing step 2 at this work center</step>             
    </Location>             
</Root>' ;  
SELECT @myDoc;          

-- insert LaborHours attribute             
SET @myDoc.modify('             
insert attribute LaborHours {".5" }             
into (/Root/Location[@LocationID=10])[1] ');           
SELECT @myDoc;          

-- insert MachineHours attribute but its value is retrived from a sql variable @Hrs             
DECLARE @Hrs FLOAT;            
SET @Hrs =.2;          
SET @myDoc.modify('             
insert attribute MachineHours {sql:variable("@Hrs") }             
into   (/Root/Location[@LocationID=10])[1] ');            
SELECT @myDoc;             

-- insert sequence of attribute nodes (note the use of ',' and ()              
-- around the attributes.             
SET @myDoc.modify('             
insert (              
           attribute SetupHours {".5" },             
           attribute SomeOtherAtt {".2"}             
        )             
into (/Root/Location[@LocationID=10])[1] ');             
SELECT @myDoc;  
GO  

D. Inserting a comment node

An XML document is initially allocated to an xml type variable in this query. Then, following the first element, XML DML is used to introduce a remark node.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;             
SET @myDoc =   
'<Root>             
    <Location LocationID="10" >             
        <step>Manufacturing step 1 at this work center</step>             
        <step>Manufacturing step 2 at this work center</step>             
    </Location>             
</Root>' ;           
SELECT @myDoc;             
SET @myDoc.modify('             
insert <!-- some comment -->             
after (/Root/Location[@LocationID=10]/step[1])[1] ');            
SELECT @myDoc;  
GO  

E. Inserting a processing instruction

An XML document is initially allocated to an xml type variable in the following query. Then, at the beginning of the document, the XML DML keyword is used to enter a processing instruction.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;  
SET @myDoc =   
'<Root>   
    <Location LocationID="10" >   
        <step>Manufacturing step 1 at this work center</step>   
        <step>Manufacturing step 2 at this work center</step>   
    </Location>   
</Root>' ;  
SELECT @myDoc ;  
SET @myDoc.modify('   
insert <?Program = "Instructions.exe" ?>   
before (/Root)[1] ') ;  
SELECT @myDoc ;  
GO  

F. Inserting data using a CDATA section

When you insert text that includes characters that are not valid in XML, such as < or >, you can use CDATA sections to insert the data as shown in the following query. The query specifies a CDATA section, but it is added as a text node with any invalid characters converted to entities. For example, < is saved as &l t.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;             
SET @myDoc =   
'<Root>             
    <ProductDescription ProductID="1" ProductName="Road Bike">             
        <Features> </Features>             
    </ProductDescription>             
</Root>' ;            
SELECT @myDoc ;            
SET @myDoc.modify('             
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>   
into  (/Root/ProductDescription/Features)[1] ') ;   
SELECT @myDoc ;  
GO  

The query inserts a text node into the element:

<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features> &lt;notxml@gt; as text &lt;/notxml&gt; or cdata </Features>  
</ProductDescription>  
</Root>       

G. Inserting text node

In this query, an XML document is first assigned to a variable of xml type. Then, XML DML is used to insert a text node as the first child of the element. The text constructor is used to specify the text.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;  
SET @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  
</Features>  
</ProductDescription>  
</Root>'  
SELECT @myDoc;  
SET @myDoc.modify('  
 insert text{"Product Catalog Description"}   
 as first into (/Root)[1]  
');  
SELECT @myDoc;  

H. Inserting a new element into an untyped xml column

The following example applies XML DML to update an XML instance stored in an xml type column:

USE AdventureWorks;  
GO  
CREATE TABLE T (i INT, x XML);  
GO  
INSERT INTO T VALUES(1,'<Root>  
    <ProductDescription ProductID="1" ProductName="Road Bike">  
        <Features>  
            <Warranty>1 year parts and labor</Warranty>  
            <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
        </Features>  
    </ProductDescription>  
</Root>');  
GO  

-- insert a new element  
UPDATE T  
SET x.modify('insert <Material>Aluminium</Material> as first  
  into   (/Root/ProductDescription/Features)[1]  
');  
GO  

The route expression must return a single target when the < Material> element node is placed. By appending a [1] to the end of the expression, this is plainly stated.

Check the update

SELECT x.query(' //ProductDescription/Features')  
FROM T;  
GO  

I. Inserting based on an if condition statement

In the following example, an IF condition is specified as part of Expression1 in the insert XML DML statement. If the condition is True, an attribute is added to the element.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;  
SET @myDoc =   
'<Root>  
    <Location LocationID="10" LaborHours="1.2" >  
        <step>Manufacturing step 1 at this work center</step>  
    <step>Manufacturing step 2 at this work center</step>  
    </Location>  
</Root>';  
SELECT @myDoc  
SET @myDoc.modify('  
insert  
if (/Root/Location[@LocationID=10])  
then attribute MachineHours {".5"}  
else ()  
    as first into   (/Root/Location[@LocationID=10])[1] ');  
SELECT @myDoc;  
GO  

The following example is similar, except that the insert XML DML statement inserts an element in the document if the condition is True. That is, if the element has less than or is equal to two child elements.

USE AdventureWorks;  
GO  
DECLARE @myDoc XML;  
SET @myDoc =   
'<Root>  
    <Location LocationID="10" LaborHours="1.2" >  
        <step>Manufacturing step 1 at this work center</step>  
        <step>Manufacturing step 2 at this work center</step>  
    </Location>  
</Root>';  
SELECT @myDoc;  
SET @myDoc.modify('  
insert  
if (count(/Root/Location/step) <= 2)  
then element step { "This is a new step" }  
else ()  
    as last into   (/Root/Location[@LocationID=10])[1] ');  
SELECT @myDoc;  
GO  

<Root>  
 <WorkCenter WorkCenterID="10" LaborHours="1.2">  
  <step>Manufacturing step 1 at this work center</step>  
  <step>Manufacturing step 2 at this work center</step>  
  <step>This is a new step</step>  
 </WorkCenter>  

J. Inserting nodes in a typed xml column

This example inserts an element and an attribute into a typed xml column with manufacturing instructions XML.

In this example, you first build a table (T) in the AdventureWorks database with a typed xml field. Then you copy a manufacturing instructions XML instance from the ProductModel table's Instructions column to table T. After that, the insertions are applied to the XML in table T.

SQL

USE AdventureWorks;  
GO            
DROP TABLE T;  
GO             
CREATE TABLE T(
  ProductModelID INT PRIMARY KEY,    
  Instructions XML (Production.ManuInstructionsSchemaCollection));  
GO  
INSERT T              
    SELECT ProductModelID, Instructions             
    FROM Production.ProductModel             
    WHERE ProductModelID=7;  
GO             
SELECT Instructions             
FROM T;  

1) insert a new manu. Location. The < Root> specified as :

expression 2 in the insert() must be singleton.

UPDATE T   
SET Instructions.modify('   
declare namespace MI="https://schemas.microsoft.com
/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";   
insert <MI:Location LocationID="1000" >   
           <MI:step>New instructions go here</MI:step>   
         </MI:Location>   
as first   
into   (/MI:root)[1]   
') ;  
  
SELECT Instructions             
FROM T ;  

2) insert attributes in the new < Location>:

UPDATE T             
SET Instructions.modify('             
declare namespace MI="https://schemas.microsoft.com/
sqlserver/2004/07/adventure-works/ProductModelManuInstructions";             
insert attribute LaborHours { "1000" }             
into (/MI:root/MI:Location[@LocationID=1000])[1] ');   
GO             
SELECT Instructions             
FROM T ;  
GO             
--cleanup             
DROP TABLE T ;  
GO 

If an ON DUPLICATE KEY UPDATE clause is included and a row to be inserted would result in a duplicate value in a UNIQUE index or PRIMARY KEY, the old row is updated.

If a duplication in the UNIQUE index or a PRIMARY KEY issue occurs when inserting a row into a table, use the MySQL INSERT ON DUPLICATE KEY UPDATE statement to update data.

The ON DUPLICATE KEY UPDATE clause in MySQL allows you to change the INSERT command to accomplish this function. If an item already exists, use this construct to change its values, or add it as a new row to the table.

An ON DUPLICATE KEY UPDATE that involves an update to an existing row is counted as two rows in MySQL. If there had been no conflict and the new record had been added, only one row would be affected. No rows would be listed as affected if an existing record was identified but the columns already had the right value.

Syntax:

The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows:

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON DUPLICATE KEY UPDATE
    <column1> = <value1>,
    <column2> = <value2>;

The ON DUPLICATE KEY UPDATE clause, which specifies a list of column-value-pair assignments in case of duplicate, is the only addition to the INSERT statement.

In essence, the expression tries to put a new row into the table first. It will update the existing row with the value supplied in the ON DUPLICATE KEY UPDATE clause if a duplicate error occurs.

MySQL returns the number of affected rows based on the action it performs:

• If a new row is added, the number of rows affected is reduced to one.
• The number of rows affected is two if an existing row is updated.
• The number of rows affected is 0 if the existing row is modified with its current values.

The VALUES() method is used to use the values from the INSERT clause in the DUPLICATE KEY UPDATE clause:

INSERT INTO table_name(c1)
VALUES(c1)
ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1;

If there is a duplicate in the UNIQUE index or PRIMARY KEY, the line above updates the value of c1 to the current value indicated by the phrase VALUES(c1) + 1.

Example 1: MySQL INSERT ON DUPLICATE KEY UPDATE example:

Let’s take a look at an example of using the INSERT ON DUPLICATE KEY UPDATE to understand how it works.

First, create a table named devices to store the network devices:

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

Next, insert rows into the devices table.

INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');

Then, query the data from the devices table to verify the insert:

SELECT id, 
    name
FROM	
    devices;

Example 2: MySQL Insert on duplicate key update example:

Now, we have three rows in the devices table.

After that, insert one more row into the devices table.

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';

Example 3: MySQL Insert or Update

MySQL creates a new row in the devices table because there is no duplicate. The preceding statement has the same effect as the following:

INSERT INTO devices(name) 
VALUES ('Printer');

Finally, insert a row with a duplicate value in the id column.

INSERT INTO devices(id,name) 
VALUES 
   (4,'Printer') 
ON DUPLICATE KEY UPDATE name = 'Central Printer';

MySQL issues the following message:

Because a row with id 4 already exists in the devices table, the statement updates the name from Printer to Central Printer.

Example 4: To demonstrate this feature, we'll imagine a table called director with the following columns and populated data:

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    latest_film VARCHAR(200)
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

The data within the table looks like this:

SELECT * FROM director;

+----+-------+-------------+
| id | name  | latest_film |
+----+-------+-------------+
|  1 | frank | NULL        |
|  2 | bob   | NULL        |
|  3 | sue   | NULL        |
+----+-------+-------------+

If you attempt to insert another row with an id column of "3", MySQL will notify you of a conflict with the existing row:

INSERT INTO director (id, name) VALUES (3, 'susan');

Output:

We may avoid this mistake if we anticipate this possibility and update the old row with the new information. This is possible thanks to the ON DUPLICATE KEY UPDATE clause:

INSERT INTO director (id, name) VALUES (3, 'susan')
ON DUPLICATE KEY UPDATE name = 'susan';

You can confirm the row has been updated with the new information by typing:

SELECT * FROM director;

+----+-------+-------------+
| id | name  | latest_film |
+----+-------+-------------+
|  1 | frank | NULL        |
|  2 | bob   | NULL        |
|  3 | susan | NULL        |
+----+-------+-------------+

The CustomerID field in our dbo.Customer table is an identity in this tutorial.

"IDENTITY(1,1)" is defined for the CustomerID field. This indicates that SQL Server will automatically increment this value by 1 as each entry is entered into the table, starting with 1.

This is significant because, as we create our INSERT statements, we must ensure that we do not include this column because SQL Server will take care of it.

Let's imagine a client was accidentally removed and you want to save their original CustomerID. The next greatest number, not the original value, would be inserted if you inserted the record like we did in the examples above.

Explanation:

Let's explain the code below:

• Section 1 - Simple SELECT statement to retrieve all of the data from the dbo.Customer table.
• Section 2 - Delete a single record from the dbo.Customer table.
• Section 3 - Simple SELECT statement to retrieve all of the data from the dbo.Customer table.
• Section 4 - Simple INSERT statement that fails because an explicit value cannot be inserted into a table with an identity column when the IDENTITY_INSERT is set to OFF, which is the default setting.

Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

• Section 5 - Same simple insert statement as in the section above, with the addition of IDENTITY_INSERT logic to permit an explicit value to be inserted into the dbo.Customer table.
• Section 6 - Simple SELECT statement to retrieve all of the data from the dbo.Customer table to verify the INSERT command in Section 5 was successful.

1 - Retrieve all of the data from the dbo.Customer table

SELECT * 
FROM dbo.Customer;
GO

2 - Delete a single record

DELETE 
FROM dbo.Customer
WHERE CustomerID = 1;
GO

3 - Verify the record was deleted

SELECT * 
FROM dbo.Customer;
GO

4 - Insert the deleted record

INSERT INTO [dbo].[Customer]
 ([CustomerID]
 ,[FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
VALUES
 (1
 ,'Jonah'
 ,'Hook'
 ,'777-777-7777'
 ,'jonah@neverdull.com'
 ,1
 ,'2011-09-01');
GO

5 - Insert the deleted record

SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT INTO [dbo].[Customer]
 ([CustomerID]
 ,[FirstName]
 ,[LastName]
 ,[PhoneNumber]
 ,[EmailAddress]
 ,[Priority]
 ,[CreateDate])
VALUES
 (1
 ,'Jonah'
 ,'Hook'
 ,'777-777-7777'
 ,'jonah@neverdull.com'
 ,1
 ,'2011-09-01');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO

6 - Verify the data

SELECT * 
FROM dbo.Customer;
GO

When data is imported into a table, the bcp command and the BULK INSERT statement default to any defaults set for the table's columns.

A standard INSERT statement, on the other hand, keeps the null value rather than inserting a default value. The INSERT... SELECT * FROM OPENROWSET(BULK...) command has the same fundamental functionality as INSERT, but it also includes a table hint for adding default values.

If a data file contains a null field, for example, the column's default value is loaded instead. You can require that null values be maintained using the bcp command and the BULK INSERT statement.

1. Keeping Null Values

The following qualifiers state that during the bulk-import operation, an empty field in the data file remains its null value rather than inheriting a default value (if any) for the table columns. Any columns not provided in the bulk-load procedure are set to NULL by default for OPENROWSET.

Command							Qualifier	Qualifier type
bcp							-k		Switch
BULK INSERT						KEEPNULLS*	Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...)		N/A		N/A

If default values are not available for BULK INSERT, the table column must be configured to enable null values.

These qualifiers prevent these bulk-import commands from checking DEFAULT definitions on a table. DEFAULT declarations are required for any concurrent INSERT statements.

Using Default Values with INSERT ... SELECT * FROM OPENROWSET(BULK...)

You can specify that the relevant table column uses the default value for an empty field in the data file (if any). Use the table hint KEEPDEFAULTS to use default values.

Example Test Conditions

The examples in this topic are based on the table, data file, and format file defined below.

Sample Table

The script below generates a test database and myNulls table. It's worth noting that the default value for the fourth table column, Kids, is 0. In Microsoft SQL Server Management Studio (SSMS), run the following Transact-SQL:

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

2.Sample Data File

Using Notepad, create an empty file D:\BCP\myNulls.bcp and insert the data below. Note that there is no value in the third record, fourth column.

• 1,Anthony,Grosse,Yes,1980-02-23
• 2,Alica,Fatnowna,No,1963-11-14
• 3,Stella,Rosenhain,,1992-03-02

Alternatively, you can execute the following PowerShell script to create and populate the data file:

PowerShell

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Sample Non-XML Format File

SQL Server support two types of format file: non-XML format and XML format. The non-XML format is the original format that is supported by earlier versions of SQL Server. Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myNulls.fmt, based on the schema of myNulls. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:

cmd

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

Important

Ensure your non-XML format file ends with a carriage return\line feed. Otherwise you will likely receive the following error message:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

3.Keep Nulls or Use Default Values During Bulk Import

The examples below use the database, datafile, and format files created above.

Using bcp and Keeping Null Values without a Format File

-k switch. At a command prompt, enter the following command:

cmd

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"
Using bcp and Keeping Null Values with a Non-XML Format File
-k and -f switches. At a command prompt, enter the following command:

cmd

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using bcp and Using Default Values without a Format File At a command prompt, enter the following command:

cmd

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data

bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results

SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Using bcp and Using Default Values with a Non-XML Format File -f switch. At a command prompt, enter the following command:

cmd

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

3.1 Using BULK INSERT and Keeping Null Values without a Format File KEEPNULLS argument.

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
	FROM 'D:\BCP\myNulls.bcp'
	WITH (
		DATAFILETYPE = 'char',  
		FIELDTERMINATOR = ',',  
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

3.2 Using BULK INSERT and Keeping Null Values with a Non-XML Format File KEEPNULLS and the FORMATFILE argument.

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt',
		KEEPNULLS
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

3.3 Using BULK INSERT and Using Default Values without a Format File

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
	  );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

3.4 Using BULK INSERT and Using Default Values with a Non-XML Format File FORMATFILE argument.

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myNulls.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

3.5 Using OPENROWSET(BULK...) and Keeping Null Values with a Non-XML Format File FORMATFILE argument.

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

3.6 Using OPENROWSET(BULK...) and Using Default Values with a Non-XML Format File KEEPDEFAULTS table hint and FORMATFILE argument.

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myNulls.bcp', 
		FORMATFILE = 'D:\BCP\myNulls.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;