SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT statement copies data (all or selected by user) from one database table and inserts it into an existing database table.

By default, the SQL INSERT INTO statement will copy only one records at a time. But we can use the SQL INSERT INTO SELECT statement to copy a single records or multiple records at once.

Note: In this way, if you copy data from source table and any existing records in the target table are unaffected by this action.

You can use other data in the same table to insert data into a table. This essentially means that you can replicate data from one table to another using the INSERT INTO SELECT query.

For load testing, you may need to quickly generate a large amount of data. In MySQL, you can select and insert into the same table.

This method allows you to quickly expand your existing tables. It provides a large number of records to choose from. Here are a few ideas for how to go about it.

If one of these fields is a main key or if a field has a uniqueness constraint, you may get a duplicate value error. Here's how to stay away from it. Without duplication, insert into the same table in MySQL.

Example 1: Let us understand this with an example:

Consider the table named Names. It has two columns:

Now if you want to add a new column storing the full names i.e First Name + Last Name.

You can add a new column Full Name with the ALTER TABLE statement.

USE [master]
GO
ALTER TABLE dbo.Names
ADD [Full Name] nchar(30)

The new column will be populated with NULL values.

You can insert new records with the following query along with the Full Name:

USE [master]
GO
INSERT INTO dbo.Names([First Name],[Last Name],[Full Name])
SELECT [First Name],[Last Name],[First Name]+[Last Name] FROM dbo.Names
SELECT * FROM dbo.Names

SQL Server insert into select from same table

Records Inserted

Now you can remove the records having the NULL values in the Full Name column.

USE [master]
GO
DELETE FROM dbo.Names WHERE [Full Name] IS NULL
SELECT * FROM dbo.Names

insert into select from same table SQL Server

Names Table: You were able to delete the records with NULL values. Now that you've constructed the Full Name column from the data, you can add it to your table.

First Name and the Last Name: You should now be able to use the INSERT INTO SELECT query in SQL Server to copy and insert data from the same table.

Example 2: In the publishers table, you can create a new entry based on the values of an existing row in the same database. Make sure you follow the pub_id column rule:

insert publishers 
select "9999", "test", city, state 
    from publishers 
    where pub_name = "New Age Books"

select * from publishers

Output:

 pub_id  pub_name              city        state 
------- --------------------- -------     ------ 
0736    New Age Books         Boston      MA 
0877    Binnet & Hardley      Washington  DC 
1389    Algodata Infosystems  Berkeley    CA 
9999    test                  Boston      MA 
 (4 rows affected) 

In the row that passed the query, the example adds the two constants ("9999" and "test"), as well as the values from the city and state columns.

For load testing, you may need to quickly generate a large amount of data. In MySQL, you can select and insert into the same table. This method allows you to quickly expand your existing tables. It provides a large number of records to choose from. Here are a few ideas for how to go about it.

Example 3:

Sometimes you may need to quickly create lot of data for load testing. You can select and insert into same table in MySQL. This approach rapidly grows your existing tables. It gives a lot of records to play with. Here are a few ways to do it.

1. Insert into same table in MySQL

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )

SELECT field1, field2, fieldN from table_name;

Example:

For a table table_name with columns a, b, c.

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
+------+------+------+

2. Insert into same table in MySQL all records from a table

INSERT INTO table_name (a,b,c) select a,b,c from table_name;

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
+------+------+------+

3. Insert into same table in MySQL some records from a table:

INSERT INTO table_name (a,b,c) select a,b,c from table_name where a=1 or a=3;

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
|   1  |   2  |   3  |
|   3  |   3  |   3  |
+------+------+------+

If one of these fields is a main key or if a field has a uniqueness constraint, you may get a duplicate value error. Here's how to stay away from it.

4. Insert into same table in MySQL without duplicates

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )
  SELECT field1, field2, fieldN from table_name
  ON DUPLICATE KEY
  UPDATE primary_key_field=< expression>;

Example: For a table table_name with columns a, b, c and c is primary key:

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
+------+------+------+

5. Insert into same table in MySQL all records from a table

INSERT INTO table_name (a,b,c) select a,b,c from table_name ON DUPLICATE KEY c=c+1;

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   1  |   2  |   4  |
|   4  |   5  |   7  |
|   7  |   8  |  10  |
+------+------+------+

6. Insert into same table in MySQL some records from a table

INSERT INTO table_name (a,b,c) select a,b,c from table_name where a=1 or a=3 ON DUPLICATE KEY c=c+1;

+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   1  |   2  |   5  |
+------+------+------+

To replicate data from one table to another without inserting duplicate entries, use the INSERT INTO SELECT query.

1. Using INSERT INTO SELECT DISTINCT

Using DISTINCT in your SELECT is the first option for identifying SQL records in SQL. We'll start by populating the Origin table to learn more about the situation. But first, let's try the incorrect approach:

-- This is wrong and will trigger duplicate key errors

INSERT INTO Origin
(Origin)
SELECT origin FROM NonItalianPastaDishes
GO

INSERT INTO Origin
(Origin)
SELECT ItalianRegion + ', ' + 'Italy'
FROM ItalianPastaDishes
GO

This will trigger the following duplicate errors:

Cannot insert a duplicate key row in object 'dbo.Origin' with unique index 'UIX_Origin'. The duplicate key value is (United States).

In object 'dbo.Origin' with unique index 'UIX_Origin', it is not possible to insert duplicate key rows. The value of the duplicate key is (Lombardy, Italy). When you try to select duplicate rows in SQL, there's an issue. I started the SQL check for previously existing duplicates by running the SELECT component of the INSERT INTO SELECT statement:

Duplicates exist. There are 5 entries for the United States.

That’s the reason for the first SQL duplicate error. To prevent it, add the DISTINCT keyword to make the result set unique. Here’s the correct code:

-- The correct way to INSERT
INSERT INTO Origin
(Origin)
SELECT DISTINCT origin FROM NonItalianPastaDishes

INSERT INTO Origin
(Origin)
SELECT DISTINCT ItalianRegion + ', ' + 'Italy'
FROM ItalianPastaDishes

It successfully inserts the records. We've completed the Origin table.

The SELECT statement will provide unique records if you use DISTINCT. It does not, however, assure that the target table is free of duplicates. It's useful when you're certain the target table doesn't already contain the values you wish to add.

So, do not run these statements more than once.

2. Using WHERE NOT IN

The PastaDishes table is then filled in. We'll start by inserting data from the ItalianPastaDishes table. The code is as follows:

INSERT INTO [dbo].[PastaDishes]
(PastaDishName,OriginID, Description)
SELECT
 a.DishName
,b.OriginID
,a.Description
FROM ItalianPastaDishes a
INNER JOIN Origin b ON a.ItalianRegion + ', ' + 'Italy' = b.Origin
WHERE a.DishName NOT IN (SELECT PastaDishName FROM PastaDishes)

We must join the Origin text rather than the OriginID because ItalianPastaDishes contains raw data. Try running the same code twice. The second time it runs, no records will be entered. It occurs as a result of the NOT IN operator in the WHERE clause. It removes records from the target table that already exist.

Next, we'll use the NonItalianPastaDishes data to populate the PastaDishes table. We won't include everything because we're just on the second paragraph of this piece.

We picked pasta dishes from the United States and the Anthaman. Here goes:

-- Insert pasta dishes from the United States (22) and the Anthaman (15) using NOT IN

INSERT INTO dbo.PastaDishes
(PastaDishName, OriginID, Description)
SELECT
 a.PastaDishName
,b.OriginID
,a.Description
FROM NonItalianPastaDishes a
INNER JOIN Origin b ON a.Origin = b.Origin
WHERE a.PastaDishName NOT IN (SELECT PastaDishName FROM PastaDishes)
 AND b.OriginID IN (15,22)

There are 9 records inserted from this statement – see Figure 2 below:

• 9 records of pasta dishes from the United States and the Anthaman.
• records of pasta dishes from the United States and the Anthaman.
• Again, if you run the code above twice, the second run won’t have records inserted.

3. Using WHERE NOT EXISTS

The NOT EXISTS clause in the WHERE clause is another approach to discover duplicates in SQL. Let's give it a shot using the same parameters as in the previous section:

Insert pasta dishes from the United States (22) and the Philippines (15) using WHERE NOT EXISTS:

INSERT INTO dbo.PastaDishes
(PastaDishName, OriginID, Description)
SELECT
 a.PastaDishName
,b.OriginID
,a.Description
FROM NonItalianPastaDishes a
INNER JOIN Origin b ON a.Origin = b.Origin
WHERE NOT EXISTS(SELECT PastaDishName FROM PastaDishes pd 
 WHERE pd.OriginID IN (15,22))
 AND b.OriginID IN (15,22)

The code above will insert the same 9 records you saw in Figure 2. It will avoid inserting the same records more than once.

4. Using IF NOT EXISTS

When deploying a table to the database, it's important to check if another table with the same name already exists to avoid duplicates. The SQL DROP TABLE IF EXISTS statement can be really useful in this situation. Using IF NOT EXISTS is another approach to avoid inserting duplicates. We'll apply the same conditions as in the previous section:

Insert pasta dishes from the United States (22) and the Anthaman (15) using IF NOT EXISTS:

IF NOT EXISTS(SELECT PastaDishName FROM PastaDishes pd 
   WHERE pd.OriginID IN (15,22))
BEGIN
	INSERT INTO dbo.PastaDishes
	(PastaDishName, OriginID, Description)
	SELECT
	 a.PastaDishName
	,b.OriginID
	,a.Description
	FROM NonItalianPastaDishes a
	INNER JOIN Origin b ON a.Origin = b.Origin
	WHERE b.OriginID IN (15,22)
END

The above code will first check for the existence of 9 records. If it returns true, INSERT will proceed.

5. Using COUNT(*) = 0

Finally, the use of COUNT(*) in the WHERE clause can also ensure you won’t insert duplicates. Here’s an example:

INSERT INTO dbo.PastaDishes
(PastaDishName, OriginID, Description)
SELECT
 a.PastaDishName
,b.OriginID
,a.Description
FROM NonItalianPastaDishes a
INNER JOIN Origin b ON a.Origin = b.Origin
WHERE b.OriginID IN (15,22)
 AND (SELECT COUNT(*) FROM PastaDishes pd 
 WHERE pd.OriginID IN (15,22)) = 0

The COUNT of records returned by the subquery above should be 0 to prevent duplicates.

Note: Using the Query Builder functionality of dbForge Studio for SQL Server, you can visually construct any query in a diagram.

Example 2: The following example will help you to understand this:

Consider the two tables Student and StudentID.

You want to copy the records from the Student table to the StudentID table without duplicates. You can write the query as:

USE [master]
GO
INSERT INTO dbo.StudentID([First Name], [Last Name], CollegeID)
SELECT [First Name],
       [Last Name],
	   [College ID]
FROM   dbo.Student
WHERE  NOT EXISTS 
(SELECT * FROM   dbo.StudentID
 WHERE  Student.[College ID] = StudentID.CollegeID)
 GO

SELECT * FROM dbo.StudentID

In the preceding query, we presume that the students' college IDs are unique and that we can compare them.

Following the comparison, the Choose query will select just those records that are not already in the StudentID table.

As a result, you can insert records from one table into another table without creating duplicates.

Example 3: The "INSERT INTO SELECT DISTINCT" pattern can be used to copy data from an existing table to a new one. After "INSERT INTO," you must enter the name of the target table, which in this case is organizations.

Then you choose which columns from the source database should be copied over — in this case, university_professors. To only duplicate over distinct organisations, you use the "DISTINCT" keyword.

INSERT INTO organizations
SELECT DISTINCT organization,
    organization_sector
FROM university_professors;

Output: INSERT 0 1287

Only 1287 records are added into the "organisations" table, according to the above output.

However, if you use "INSERT INTO SELECT" without the "DISTINCT" clause, duplicate records will also be copied across.

INSERT INTO organizations
SELECT organization,
    organization_sector
FROM university_professors;

Output: INSERT 0 1377

Migrating Data Into a New Table

Let's migrate the data into new tables. You'll use the following pattern:

INSERT INTO ...
SELECT DISTINCT ...
FROM ...;

It can be broken up into two parts:

First part:

SELECT DISTINCT column_name1, column_name2, ...
FROM table_a;

This selects all distinct values in table table_a – nothing new for you.

-- Insert unique professors into the new table
INSERT INTO professors
SELECT DISTINCT firstname, lastname, university_shortname
FROM university_professors;

-- Doublecheck the contents of professors
SELECT *
FROM professors;
Second part:
INSERT INTO table_b ...;

This component should be appended to the first so that all distinct rows from table_a are inserted into table_b.

Last but not least, once you've filled in all of the blanks, it's critical that you run all of the code at the same time.

-- Insert unique affiliations into the new table
INSERT INTO affiliations
SELECT DISTINCT firstname, lastname, function, organization
FROM university_professors;

-- Doublecheck the contents of affiliations
SELECT *
FROM affiliations;

You can also used to union all the selected tables.

Syntax:

To perform multiple inserts, the syntax is as follows:

insert into yourTableName(yourColumnName1,yourColumnName2,yourColumnName3,..N)
select yourValue1 as yourColumnName1,yourValue2 as yourColumnName2,yourValue3 as yourColumnName3,......N
union
select yourValue1 as yourColumnName1,yourValue2 as yourColumnName2,yourValue3 as yourColumnName3,......N
.
.
N

Example 1: Insert some records in the table using insert command :

insert into DemoTable1936(StudentId,StudentName,StudentCountryName)
select 1001 as StudentId,'Chris' as StudentName,'US' as StudentCountryName
union
select 1002 as StudentId,'Robert' as StudentName,'UK' as StudentCountryName
union
select 1003 as StudentId,'David' as StudentName,'AUS' as StudentCountryName;

Display all records from the table using select statement:

select * from DemoTable1936;

Output:

+-----------+-------------+--------------------+
| StudentId | StudentName | StudentCountryName |
+-----------+-------------+--------------------+
|      1001 | Chris       |               US   |
|      1002 | Robert      |               UK   |
|      1003 | David       |              AUS   |
+-----------+-------------+--------------------+

Example 2: The phrase INSERT INTO appears several times. DBAs frequently copy and paste to save time. There is a solution to this that I frequently employ. I use UNION ALL and INSERT INTO … SELECT… Clauses. There isn't much of a difference in terms of effectiveness. It doesn't matter whether there's a performance difference because I'm only using this as a one-time insert script. Instead of copying and pasting, I prefer to write this manner since it keeps my focus on the subject at hand. To novice coders, I explained the following script. He was very happy.

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.

Syntax:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {SELECT ... | TABLE table_name}
    [ON DUPLICATE KEY UPDATE assignment_list]

value: {expr | DEFAULT}

assignment: col_name = value

assignment_list: assignment [, assignment] ...

With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables.

Example 1:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Beginning with MySQL, you can use a TABLE statement in place of SELECT, as shown here:

INSERT INTO ta TABLE tb;

SELECT * FROM tb is similar to TABLE tb. It's helpful when you want to insert all columns from the source table into the target table without having to use WHERE. Furthermore, using the ORDER BY clause, the rows from TABLE can be arranged by one or more columns, and the amount of rows entered can be limited using the LIMIT clause.

The following conditions apply to INSERT... SELECT statements, as well as INSERT... TABLE statements, unless otherwise noted:

Disregard allows you to ignore rows that would result in duplicate-key violations.

The INSERT statement's target table might be in the FROM clause of the SELECT part of the query, or it could be the table named by TABLE. In a subquery, however, you cannot insert into a table and then select from the same table.

MySQL generates an internal temporary table to keep the rows from the SELECT and then inserts those rows into the target table when choosing from and inserting into the same table. When t is a TEMPORARY table, however, you can't use INSERT INTO t... SELECT... FROM t because TEMPORARY tables can't be referenced twice in the same statement. You can't use INSERT INTO t... TABLE t for the same reason.

AUTO_INCREMENT columns work as usual.

MySQL does not allow concurrent inserts for INSERT... SELECT or INSERT... TABLE operations to ensure that the binary log may be utilised to recreate the original tables.

Provide a unique alias for each table used in the SELECT section, and qualify column names in that part with the suitable alias, to avoid unclear column reference issues when the SELECT and the INSERT refer to the same table.

The TABLE statement does not support aliases.

With a PARTITION clause after the table name, you can specify which partitions or subpartitions (or both) of the source or target table (or both) should be used. Rows are selected only from the partitions or subpartitions mentioned in the partition list when PARTITION is used with the name of the source table in the SELECT portion of the statement. It must be feasible to insert all rows selected into the partitions or subpartitions indicated in the partition list after the option when PARTITION is used with the name of the target table for the INSERT portion of the statement. The INSERT... SELECT statement will fail otherwise.

TABLE does not support a PARTITION clause.

For conditions under which the SELECT columns can be referred to in an ON DUPLICATE KEY UPDATE clause, see "INSERT... ON DUPLICATE KEY UPDATE Statement." This works for INSERT... TABLE as well.

The order in which rows are returned by a SELECT or TABLE command without an ORDER BY clause is nondeterministic. This means that when employing replication, there's no guarantee that a SELECT will return results in the same order on the source and replica, which could cause inconsistencies. To avoid this, always use an ORDER BY clause in INSERT... SELECT or INSERT... TABLE statements that are to be replicated to produce the same row order on the source and replica.

INSERT... SELECT ON DUPLICATE KEY UPDATE and INSERT IGNORE... have been disabled as a result of this problem. For statement-based replication, SELECT statements are marked as risky. When using statement-based mode, such statements generate a warning in the error log, and when using MIXED mode, they are recorded to the binary log using the row-based format.

Example 2: INSERT INTO SELECT examples:

Let us create another table Customers with the following query.

CREATE TABLE Customers
(ID   INT, 
 Name VARCHAR(20)
);

All records from the Employees table should be inserted into the Customers table. To accomplish so, we'll use the SQL INSERT INTO SELECT command.

INSERT INTO Customers
       SELECT *
       FROM Employees;

It inserts all records into the Customers table. We can verify the records in Customers table are similar to the Employees table.

In SQL Server, use the insert into select statement with auto-increment. Create an identity column in the new table if you wish to import data from one table to another with automatically incrementing values.

The key values for all entries are created for reference when you establish an identity column in the table. The seed value and increment value at the time of column formation are used to automatically increment these key values.

Example: Consider the following Customers table. We will assign new customer IDs in the NewCustomers table with auto-increment values.

SQL Server insert into select with auto-increment

Customers Table

To begin, create a table where you want to import the data using the IDENTITY function and the auto-increment option.

USE [master]
GO

DROP TABLE IF EXISTS NewCustomers

CREATE TABLE NewCustomers(
CustomerID int IDENTITY(1000,1),
[Customer Name] nchar(30)
)

INSERT INTO NewCustomers([Customer Name])
SELECT [Customer Name] FROM Customers

SELECT * FROM NewCustomers

In the preceding example, we defined a CustomerID identity column with a 1000 starting value and a 1 increment value. This indicates that the values will begin at 1000 and will auto-increment from there. For the identification column, you can define your own set of values.

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.

An INSERT... SELECT... ON DUPLICATE KEY UPDATE statement that uses VALUES() in the UPDATE clause, such as this one, produces a warning starting with MySQL 8.0.20:

INSERT INTO t1
  SELECT c, c+d FROM t2
  ON DUPLICATE KEY UPDATE b = VALUES(b);

You can eliminate such warnings by using a subquery instead, like this:

INSERT INTO t1
  SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt
  ON DUPLICATE KEY UPDATE b = e;

As previously noted, you can use a SET clause to use row and column aliases. In the two INSERT statements, use SET instead of VALUES. The following are examples of ON DUPLICATE KEY UPDATE statements:

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

The table name and the row alias must not be the same. If column aliases aren't used, or if they're the same as the column names, the ON DUPLICATE KEY UPDATE clause must use the row alias to separate them. Column aliases must be distinct from the row aliases they apply to (that is, no column aliases referring to columns of the same row may be the same).

These criteria apply to valid types of SELECT query expressions that you can refer to in an ON DUPLICATE KEY UPDATE clause for INSERT... SELECT statements:

• Column references from queries on a single table, which may or may not be a derived table.
• Column references from join queries across many tables.
• Columns from DISTINCT searches are referenced.

As long as the SELECT doesn't employ GROUP BY, references to columns in other tables are allowed. One unintended consequence is that nonunique column names must be qualified.

It is not possible to make references to columns from a UNION. To get around this limitation, recast the UNION as a derived table and consider the rows as a single-table result set. This statement, for example, results in the following error:

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

Instead, use an equivalent statement that rewrites the UNION as a derived table:

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

References to columns from GROUP BY queries can also be made using the technique of rewriting a query as a derived table.

Because the outcomes of INSERT... SELECT statements are dependent on the ordering of rows from the SELECT, which cannot always be guaranteed, it is possible for the source and replica to diverge when recording INSERT... SELECT ON DUPLICATE KEY UPDATE statements. As a result, statements like INSERT... SELECT ON DUPLICATE KEY UPDATE are marked as risky for statement-based replication. When using statement-based mode, such statements generate a warning in the error log, and when using MIXED mode, they are recorded to the binary log using the row-based format. An INSERT... ON DUPLICATE KEY UPDATE statement can also be used to update a table with multiple unique or main keys.

With an identity column, utilize the INSERT INTO SELECT query. We've set up a scenario in which we'll duplicate data from one table to another using an identity field.

• Assume you wish to create a new table to allocate new employee IDs to the employees. Also, the new Employee ID field should be used as an identity column.
• The data from the old table must be imported into a new table.

For example, we've established a table called Employees to store employee information.

insert into select with identity column in sql server.

Employees Table

We will create a new table NewEmployees where we will use an identity column and import the employees’ data from the Employees table.

USE [master]
GO
DROP TABLE IF EXISTS dbo.NewEmployees
CREATE TABLE dbo.NewEmployees(
EmpID int IDENTITY(10,1),
EmpName nchar(30),
EmpDep nchar(20)
)

INSERT INTO dbo.NewEmployees(EmpName, EmpDep)
SELECT EmpName, EmpDep FROM dbo.Employees

SELECT * FROM dbo.NewEmployees

You can observe that despite we did not mention the EmpID column values, the values are automtically assigned because of being an identity column.

Example 1: To insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with the same customer_id.

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers) +1, 'jim', 'sock')

I keep getting the following error:

Also, how can I prevent two customers with the same customer_id from being created at the same time?

You can't edit and select from the same table in the same query, that's correct. You'd have to run the aforementioned questions in two distinct inquiries.

The optimal method is to utilise a transaction, but if you aren't utilising innodb tables, the next best option is to lock the tables first and then run your queries. So:

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;
Grab the max id and then perform the insert

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')

unlock tables;

Example 2: I am working with the following Knowledge Tree table in MySQL - it holds reference data.

CREATE TABLE `document_types_lookup` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(100),
    `disabled` BIT DEFAULT'0' NOT NULL
);

ALTER TABLE `document_types_lookup` ADD PRIMARY KEY (`id`);

I needed to insert a few thousand rows, but since there is no auto-increment field for the ID, I needed SQL that could increment ID for me - using SQL only, no script. I found a bug report (Allow INSERT...SELECT MAX(x)+1 from the same table) that had the below snippet that used select (max(id)+1) to increment ID for an insert.

insert into foo(lfd) select (max(lfd)+1) from foo;

And while I could get that statement by itself to work (inserting only ID into a record), I could not not adapt it to add the rest of the record values.

insert into
   document_types_lookup(id, name, disabled)
values
   (select (max(id)+1) from document_types_lookup, 'a name', false);

I always got the below error.

Executing:

insert into document_types_lookup(id, name, disabled) 
values (select (max(id)+1) from document_types_lookup, 'a name', false)

So, my work-around is to use two statements instead of one: an insert followed by an update, as below.

insert into
   document_types_lookup(id)
   select (max(id)+1) from document_types_lookup;

update
   document_types_lookup
set
   name = 'CORP_HMN_000_CA_',
   disabled = false
where
   name is null;

To begin with, it isn't thread safe, which means that using select (max(id)+1) in an insert is risky if multiple individuals are updating the table at the same time. You have no guarantee that a new record will have been added by someone else between the time you selected max ID + 1 and the time the insert really happens, meaning that when your insert statement eventually strikes, your max ID + 1 will no longer be unique. You can avoid this by locking the table before inserting and then releasing it.

The second reason this is a risky script is because it believes there will only ever be one record with a null name; it does so because I couldn't think of any other way to figure out which record I had just put.

So, this script only works because a) I knew I'd be the only one changing the table at the moment, and b) I knew name would be a value for all records except the one I'd just put.

Example 1: In a MySQL database, I need to read data from one table and insert it into several rows in another table.

Table 1 looks like:

ID, name, e-mail, phone, city, ..., ....

In Table 2 I need to insert data like:

(row1) ID, "name", name
(row2) ID, "e-mail, e-mail
(row3) ID, "phone", phone
...
...

Table 1 has about 3000 rows

Syntax :

INSERT INTO table2
     (col1, col2, coln)
     SELECT col1, col2, coln
     FROM table1;

If my understanding of your question is true, you want to run a query on table1 that returns numerous rows, then insert those rows into table2 in a single loop. The INSERT INTO SELECT statement is as follows:

INSERT INTO table2
  (name, email, phone)
  SELECT name, email, phone
  FROM table1;

It can be modified to grab specific results as well:

INSERT INTO table2
  (name, email, phone)
  SELECT name, email, phone
  FROM table1
  WHERE name = 'target person';

Example 2: Insert Multiple Rows from SELECT:

You may also use the results of a SELECT query to insert numerous rows of data into your table.

The SQL query syntax for copying data from one table to another with the INSERT INTO statement is shown below.

INSERT INTO table1 (column1, column2, ...)
select column1, column2, ...
from table2

We select column1, column2,... from table2 and insert them into table1 in the given query.

Please keep in mind that the columns in the INSERT INTO and SELECT statements must have the same name and order. You'll get an error if you don't.

Here is the SQL query to copy data from employees table to employees2 table.

insert into employees2(id, first_name, last_name)
select id, first_name, last_name
from employees;

select * from employees2;

Output:

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

Use the INSERT ALL statement to add several records to a single or more tables using a single INSERT statement.

Syntax: INSERT ALL

INTO table_name (column_name1,column_name2,...)
VALUES(value1,value2,...)

INTO table_name (column_name1,column_name2,...)
VALUES(value1,value2,...)

Subquery

According to the INSERT ALL syntax, each value expression value1, value2 must relate to a column returned by the subquery's select list. Use SELECT * FROM Dual; as a subquery if you want to provide literal values.

Example 1: The following is a list of resources. INSERT ALL will add three new records to the Employee table, each with its own set of columns.

SQL Script: Insert Multiple Records in SQL

INSERT ALL
  INTO Employee(EmpId, FirstName, LastName)VALUES(1,'Renske','Ladwig')
  INTO Employee(EmpId, FirstName, LastName, PhoneNo) VALUES(2,'Laura','Bissot', '123.456.444')
  INTO Employee(EmpId, FirstName, LastName, Salary) VALUES(3, 'Kevin','Weiss', 25000)

SELECT * FROM Dual;

After executing the above query, the Employee table will look like below.

EmpId	FirstName	LastName	Email	PhoneNo	        Salary
1	'Renske'	'Ladwig'			
2	'Laura'	        'Bissot'		'123.456.444'	
3	'Kevin'	         'Weiss'			         25000

The INSERT ALL can be used to insert multiple records into multiple tables. Bellow, the INSERT ALL statement will insert one record to the Employee table, and two records to the Customer table with a different set of columns.

INSERT ALL
  INTO Employee (EmpId, FirstName, LastName) VALUES(4,'Sachin','Tendulkar')
  INTO Customers(CustId, FirstName, LastName, Country) VALUES(1, 'Laura','Bissot', ‘USA')
  INTO Customers(CustId, FirstName, LastName, Gender, Country) VALUES(2, 'Kevin','Weiss','M','ROME')

SELECT * FROM Dual;

We can also select data from another table to populate a table with many records, as long as the other table provides the essential fields to complete the first table.

SQL Script: Insert Multiple Records in Multiple Tables in SQLInsert Records From Another Table

INSERT INTO Employee(EmpId, FirstName, LastName)
 
 SELECT CustId, FirstName, LastName FROM Customer

The above INSERT statement copies data from the Customer dataset to the Employee table, replacing CustId values with EmpId values.

Example 2: SQL Joins allow you to insert data from various tables. You'll see an example where we utilised an inner join to insert data into a new table from two tables.

Consider the following two tables:

sql server insert into select from multiple tables

We have created two tables:

Employees

Department

We will insert the column values from these two tables to a new table.

If we have a new table named EmployeeDetails into which we want to store the employee’s ID, employee’s name, employee’s department, firstly, we will create the table as:

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

Then we will insert records into it from the two tables i.e. Employees and Department using INNER JOIN.

USE [master]
GO
INSERT INTO dbo.EmployeeDetails
SELECT Employees.EmpID,Employees.EmpName, Department.DepName
FROM Employees INNER JOIN Department
ON Employees.DepID= Department.DepID
GO

SELECT * FROM dbo.EmployeeDetails

The EmployeeDetails table is filled with values, as you can see.

As a result, you should know how to use SQL Server's INSERT INTO SELECT query.

Main Article :- Sql difference between SELECT INTO and INSERT INTO SELECT

Data is frequently moved from one database table to another by SQL Server developers. This could be one of the temporary tables or one of the current tables.

We have two possibilities for accomplishing this: use a SELECT INTO SQL query or use an INSERT INTO SQL query. Both provide the same functionality, but there are two significant distinctions between them that may influence your choice of which to utilise.

Although INSERT INTO SELECT and SELECT INTO are relatively similar procedures, there are some significant distinctions between them. I occasionally observe folks who are perplexed by the similarities while overlooking the distinctions.

Similarities

• They look similar (I think this one throws people a lot.)
• They are both part of larger commands.
• They both insert rows into a table.
• Both can be minimally logged under the right circumstances.

Differences

• Each of the columns in the table will have a name if all of the columns in the query are named.
• The source query determines the data type and nullability.
• If one of the source columns is an identity column and certain conditions are met (no JOINs in the query, for example), the new table's column will be an identity as well.

Let's discuss with an example

We have a source table with a 50-character VARCHAR column. When we use SELECT INTO, we have a target table with a VARCHAR column with a size of 50. If you try to insert data in the VARCHAR column that is larger than 50 characters, you'll get an error message. String or binary data will be truncated. Of course, this would have been the case under normal circumstances as well, but this error has the potential to influence your decision on which command to use.

1. SELECT INTO

The first distinction is that when moving data from a source table to a target table, SELECT INTO does not need the target table to exist in your database. When this command is used, a new table will be generated automatically.

SELECT INTO makes a new table and populates it with data.

If you try to use SELECT INTO on an existing table, it will fail because it will try to create a new table with the same name, resulting in an error.

When we use the SELECT INTO statement, the table structure is immediately created.

The SELECT...INTO statement will produce new pages for table construction in the same way that ordinary tables do, and they will be physically removed when the temporary table is discarded.

SELECT INTO Syntax :

Copy all columns into new table in same database:

SELECT *
INTO new_table
FROM table_name;

Copy only the columns we want into the new table in same database:

SELECT column_name1,column_name2,..
INTO new_table
FROM table_name;

Example 1: SQL Server SELECT INTO Example:

The SELECT...INTO command, which performs better than the INSERT...SELECT command, is an alternative solution.

SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'
DECLARE @input_xml XML

SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)

SELECT f.x.value('.', 'BIGINT') AS Id
INTO #list_to_table
FROM @input_xml.nodes('/root/x') f(x)

DROP TABLE #list_to_table

SQL Server parse and compile time:

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 0 ms.
(5 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Example 2: SQL Server INSERT...SELECT vs. SELECT...INTO with a Larger Data Set:

Because the examples above are so small, we'll use the example below to illustrate how this works with a lot of data. There will be almost a million rows in this test.

SQL Server INSERT... SELECT:

CREATE TABLE #EMPLOYEEEEE
    (Emp_id BIGINT, 
     f_name NVARCHAR(100), 
     l_name NVARCHAR(100),
     Email NVARCHAR(100),
     is_active BIT)
  
INSERT INTO #EMPLOYEE

SELECT Emp_id, f_name, l_name, Email, is_active 
FROM employee

SQL Server parse and compile time:

SQL Server Execution Times:

CPU time = 1407 ms, elapsed time = 3241 ms.
(1727264 row(s) affected)

SQL Server SELECT...INTO

SELECT Emp_id, f_name, l_name, Email, is_activee
INTO #EMPLOYEE
FROM employee

SQL Server parse and compile time:

SQL Server Execution Times:

CPU time = 1499 ms,  elapsed time = 489 ms.

(1727264 row(s) affected)

Results and Analysis

As we can see the SELECT...INTO was considerably faster 489ms compared to 3241ms.

Example 2: SELECT INTO Examples:

Create a backup copy of Customers:

SELECT *
INTO Users_Backup
FROM Users;

Copy only a few columns into the new table:

SELECT User_Name, User_Contact
INTO Users_Backup
FROM Users;

2. INSERT INTO SELECT

For insert/update/delete operations, the INSERT...INTO command will reuse data pages that have been produced in cache. When it is dropped, it will also truncate the table.

INSERT INTO SELECT is a command that inserts data into an existing table.

Before data can be transferred from source to target table, INSERT INTO requires the existence of a target table.

To use Enter INTO, we must first construct a target table and then insert the data into it.

INSERT INTO Syntax :

Insert data from one table into another for all columns,

INSERT INTO table2
SELECT * FROM table1;

Insert data from one table into another for specific columns,

INSERT INTO table2
column1, column2..
SELECT column1, column2, ..
FROM table1;

SQL Server INSERT INTO Example

SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'
DECLARE @input_xml XML

CREATE TABLE #list_to_table(Id BIGINT)

SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)    
 
INSERT INTO #list_to_table
SELECT f.x.value('.', 'BIGINT') AS Id
FROM @input_xml.nodes('/root/x') f(x)

DROP TABLE #list_to_table

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 2 ms.

Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 0 ms.

(5 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

When the input parameter has a large number of comma separated values, the code can be postponed for a few seconds while the data is inserted into the temp table.

Example 2: INSERT INTO Examples:

INSERT INTO users (user_name, location)
SELECT customer_name, location FROM customers;  

Remove the identification column from the INSERT INTO SELECT query.

Assume the table TableIdentity, which has a column named identity.

We're going to copy the data into a new table called NewTableIdentity.

Example: Except for the identification column, the data from the TableIdentity table will be copied to the NewTableIdentity table using the following query:

USE [master]
GO

DROP TABLE IF EXISTS dbo.NewTableIdentity

CREATE TABLE dbo.NewTableIdentity(
[Employee Name] nchar(20),
[Employee Department] nchar(20)
)

INSERT INTO dbo.NewTableIdentity
([Employee Name],[Employee Department])
SELECT EmpName, EmpDep FROM dbo.TableIdentity
GO

SELECT * FROM dbo.NewTableIdentity

The records are successfully added without the identification column, as you can see in the output.

We can insert many rows of data into a table using an INSERT...SELECT command, which is the result of a SELECT operation that can obtain data from one or more tables.

The INSERT INTO statement can also be used to insert all or some entries from another table into the table. Using the SQL SELECT command, the rows of another table will be fetched depending on one or more criteria.

Example 1:

Sample table: agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

Sample table: agentbangalore

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To add records of 'agents' table into 'agentbangalore' table with the following condition :

The 'working_area' of 'agents' table must be 'Bangalore', the following SQL statement can be used:

SQL Code:

INSERT INTO agentbangalore
SELECT * FROM agents
WHERE  working_area="Bangalore";

Example 2: If your database has two tables and you wish to SELECT data from one and INSERT it into the other, you can do it with an INSERT...SELECT statement.

Let's take a few examples to understand how this can be done.

Tables with same columns:

If there is a table Target with columns A, B, and C and another table SOURCE with columns A, B, and C, and we want to insert data from the table Source into the table TARGET, then we can use the following:

Syntax:

INSERT INTO Target(A, B, C)
 
SELECT A, B, C FROM Source

We can also use the WHERE clause in the SELECT query to get specific row of data. Here is an example for that:

INSERT INTO Target(A, B, C)
  SELECT A, B, C
  FROM Source WHERE A='some value';

Inserting extra values along with SELECT statement result:

If you want to INSERT some constant values in some columns of table Target, as well as some values from table Source's columns, follow these steps:

INSERT INTO Target (A, B, C) 
SELECT  'some other value', 
        B,
        C
from Source;

In the following query, we insert a constant string value in column A of table Target, whereas the values in the other two columns come from the Source table's columns.

We can use the WHERE clause to get a specific row of data from the Source table once more.

The following is the effect of inserting empty or NULL values using a SELECT statement:

If you want to INSERT some empty or NULL values in some columns of table Target along with some data from table Source, you can do it as follows:

INSERT INTO Target (A, B, C) 
SELECT  '', 
        NULL,
        C
from Source;

We are adding an empty value in column A of table Target and a NULL value in column B of table Target using the above query. If we like, we can also utilise the WHERE clause here.

A temporary table, often known as a temp table, is a user-created table used to store a subset of data from one or more actual tables. Temp tables are useful for storing vast volumes of data that would otherwise require multiple queries to filter. Temporary tables remain only as long as the connection that created them is active, or until the user or process actively drops them, and they are stored in the tempdb system database.

The INSERT INTO SELECT statement extracts data from one table and inserts it into another. For example, if we wish to use the INSERT INTO SELECT command to replicate the data from the Location table into a temp table, we must first specify the temporary table and then insert the data.

Example 1:

---Declare the temporary table---
CREATE TABLE #CopyLocation(
    LocationID smallint  NOT NULL,
    Name nvarchar(50) NOT NULL,
    CostRate smallmoney NOT NULL,
    Availability decimal (8, 2) NOT NULL,
    ModifiedDate datetime NOT NULL)
 
---Copy data into the temporary table---
    INSERT INTO #CopyLocation
    SELECT * FROM Production.Location

---Select data from the temporary table---
    SELECT * FROM #CopyLocation

Example 2: The process of constructing a physical table to hold data and then populating it with data from the physical table in a temp table.

The first step is to construct and populate a physical database table with data. The following script will create a table named employee. The PRIMARY KEY in the table will be the employee ID column, which will be an auto incremented value. The table will also include the job title, as well as the last name, first name, and hire date.

CREATE TABLE employee
(
emp_id INT IDENTITY PRIMARY KEY,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(30) NOT NULL,
hire_date DATETIME NOT NULL,
job_title VARCHAR(50) NOT NULL
)

The next step is to add some data to the newly created employee table so that we can use it. The INSERT INTO function call will be used in the following script to do this.

INSERT INTO employee
VALUES ('Smith', 'James', '3/1/2016', 'Staff Accountant'),
('Williams', 'Roberta', '2/7/2004', 'Sr. Software Engineer'),
('Weinberg', 'Jeff', '1/2/2007', 'Human Resource Manger'),
('Franklin', 'Victoria', '7/2/2010', 'Operations Manager'),
('Armstrong', 'Williams', '11/14/2012', 'Database Administrator'),
('Cromley', 'Eric', '9/9/2009', 'Recruting Manager'),
('Richardson','John', '2/11/2007', 'Safety Clerk'),
('Horton', 'Michelle','6/12/2009','Accounting Manager'),
('Washington','Mark','8/19/2014', 'HelpDesk Technician')

Now that the physical table has been created and populated, you can easily query the table.

SELECT * FROM employee

There are two ways to go about creating and populating a temp table.

Method 1: The first, and most straightforward, technique is to SELECT the data into the temp table. The temp table is practically created on the fly with this method.

The example below will create a temporary table and insert the last_name, first_name, hire_date and job_title of all employees in the physical employee table with a hire_date that is greater than 1/1/2010.

SELECT last_name, first_name, hire_date, job_title 
INTO #tmp_employees
FROM dbo.employee
WHERE hire_date > '1/1/2010';

You can query the temp table just like any physical table.

SELECT * from #tmp_employees

There are two options for removing the temporary table, as previously mentioned. The first step is to disconnect from the database where the temp table was created. The other option is to use the command below.

DROP TABLE #tmp_employees

This method is more practical in most cases since you can use the drop command in conjunction with a validation check when creating temp tables in stored procedures to check whether the table already exists and drop it before running the function. This form of logic can be seen in the instance below.

IF OBJECT_ID('tempdb..#tmp_employees') IS NOT NULL
DROP TABLE #tmp_employees

Method 2: The second approach for generating and populating a temp table is first creating the table and then populating it using the INSERT INTO command. These actions are similar to those required to generate and populate the physical table mentioned before.

You'll realize in the following script that you must assign a datatype to each column you create while establishing the temp table. The datatype of the physical table or tables from which the data will be fetched should be matched. When creating the temp table, this will prevent any potential truncation issues.

CREATE TABLE #tmp_employees
(
last_name VARCHAR(30),
first_name VARCHAR(30),
hire_date DATETIME,
job_title VARCHAR(50)
)
 
INSERT INTO #tmp_employees
SELECT last_name,
    first_name,
    hire_date,
    job_title
FROM dbo.employee
WHERE hire_date < '1/1/2010'

After creating the table the script uses the INSERT INTO command to populate #tmp_employees with the last_name, first_name, hire_date and job_title of all employees from the physical employee table who have a hire_date less than 1/1/2010.

Using the same select statement as before, you may query the data.

As previously stated, only the connection that generated them has permission to these types of interim tables. All you have to do to make a globally accessible temp table is put double hash marks in front of the table name. When the user or method that created the global temp tables is no longer active, they will also expire. A global temp table, on the other hand, can be accessed by any database user while it is active.

The following script builds a global temporary table using the same logic as the first example.

SELECT last_name, first_name, hire_date, job_title 
INTO ##tmp_employees
FROM dbo.employee
WHERE hire_date > '1/1/2010'

Just like normal temporary and physical tables, the global temp table can be queried in the same manner.

SELECT * FROM ##tmp_employees

I hope the procedures listed above have given you some helpful insight into how to build and manage temporary tables in SQL Server, whether you are new to SQL or the concept of temporary tables.

Insert top(n)

During a recent training session with one of my clients, I was questioned about the TOP clause enhancement. 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. Allow me to share this with you all, and please let me know what you think.

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

Today, we'll talk about the second method, which is the enhancement of TOP and INSERT. It's also fascinating to note the differences between the two ways. Let's take a look at a real-life scenario to see what's going on in each 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.

Use the term EXISTS. EXISTS accepts a subquery and returns a boolean that is TRUE if the subquery contains any rows. As a result, we may verify for the availability of our unique insert in the table we're putting into.

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.

Example 1:

INSERT INTO dbo.table (field) 
SELECT 'field'
WHERE NOT EXISTS 
    (SELECT field 
     FROM dbo.table
     WHERE field = 'field')

Example 2:

INSERT INTO dbo.Customer (firstname, lastname, phone) 
SELECT 'Mitch', 'Valenta', '555-867-5309'
WHERE NOT EXISTS 
    (SELECT firstname, lastname 
     FROM dbo.Customer 
     WHERE firstname = 'Mitch' AND lastname = 'Valenta')

When you look at Example 2, you can see that the subquery is checking to see if there is a record for Mitch Valenta in the customer table. If no values are detected, the primary query select returns true values, which are placed into the Customer table. I would have to make two different calls to the database if I didn't use the EXISTS, one for the subquery and one for the main query.

Example 3: 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's take a look at the illustration query below for 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:-

Here in the subquery with the NOT EXISTS clause, we are selecting the record from table customer_details. If the row does not exist in the table, then FALSE will be returned. Since there is a ‘NOT‘ keyword before EXISTS keyword, the query will INSERT the row.

SELECT * FROM customer_details;

shows that the record has been inserted.

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;

Example 4: If you had a table called clients with the primary key client id, for instance, you could use the SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id AS client_id, supplier_name AS client_name, 'advertising' AS client_type
FROM suppliers

WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

With a subselect, this SQL INSERT query adds multiple records.

If you only wanted to insert a single record, use the SQL INSERT command below:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
   FROM clients
   WHERE clients.client_id = 10345);

Even if your values are not currently saved in a table, you can utilize the dual table to input them in a select query.

In SQL, you can use the insert into select statement with auto-increment. Create an identity column in the new table if you wish to import data from one table to another with automatically incrementing values.

The key values for all entries are created for reference when you establish an identity column in the table. The seed value and increment value at the time of column formation are used to automatically increment these key values.

The IDENTITY function can be used to construct an identity column. With the help of an example, we will be able to comprehend this.

Take a look at the Customers table below. In the NewCustomers table, we'll assign new customer IDs with auto-increment values.

SQL Server insert into select with auto-increment

Customers Table

To begin, create a table where you want to import the data using the IDENTITY function and the auto-increment option.

USE [master]
GO
DROP TABLE IF EXISTS NewCustomers
CREATE TABLE NewCustomers(
CustomerID int IDENTITY(1000,1),
[Customer Name] nchar(30)
)

INSERT INTO NewCustomers([Customer Name])
SELECT [Customer Name] FROM Customers

SELECT * FROM NewCustomers

In the preceding example, we defined a CustomerID identity column with a 1000 starting value and a 1 increment value. This indicates that the values will begin at 1000 and will auto-increment from there. For the identification column, you can specify your own set of values.

As you can see, the values are auto-incremented, so we don't have to manually enter them.

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.

1.INSERT single record if NOT EXISTS in MySQL

In this section, we will be inserting a record to the table customer_details, but we will check if the customer_name already exists. If yes- then it will not insert the record; else, 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;

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;

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

2.INSERT multiple record if NOT EXISTS in MySQL

What if we have more than one record to insert and want to make sure that no record with the identical 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;

both the records have been inserted to table customer_details.