INSERT INTO VALUES 여러개 - INSERT INTO VALUES yeoleogae

여러 개의 INSERT문을 한 번에 처리

먼저 실습을 위한 대상 테이블을 만들어 보자.

입력

    CREATE TABLE ex7_3 (
           emp_id    NUMBER,
           emp_name  VARCHAR2(100));

결과

    table ex7_3이(가) 생성되었습니다.

입력

    CREATE TABLE ex7_4 (
           emp_id    NUMBER,
           emp_name  VARCHAR2(100));

결과

    table EX7_4이(가) 생성되었습니다.

ex7_3 테이블에 두 개의 로우를 입력해야 한다면 다음과 같이 두 개의 INSERT문을 사용하면 된다.

입력

    INSERT INTO ex7_3 VALUES (101, '홍길동');

결과

    1개 행 이(가) 삽입되었습니다.

입력

    INSERT INTO ex7_3 VALUES (102, '김유신');

결과

    1개 행 이(가) 삽입되었습니다.

하지만 다음과 같이 INSERT ALL 구문을 사용하면 한 번에 여러 개의 로우를 입력할 수 있다.

입력

    INSERT ALL
      INTO ex7_3 VALUES (103, '강감찬')
      INTO ex7_3 VALUES (104, '연개소문')
    SELECT *
      FROM DUAL;

결과

    2개 행 이(가) 삽입되었습니다.

다중 테이블 INSERT 구문을 사용할 때 반드시 서브 쿼리가 동반되어야 하므로 위 쿼리에서는 맨 마지막에 의미 없는 DUAL을 선택하도록 했다. 다음과 같이 문장을 작성해도 위와 동일한 결과를 얻을 수 있다.

입력

    INSERT ALL
      INTO ex7_3 VALUES (emp_id, emp_name)
    SELECT 103 emp_id, '강감찬' emp_name
      FROM DUAL
     UNION ALL
    SELECT 104 emp_id, '연개소문' emp_name
      FROM DUAL;

결과

    2개 행 이(가) 삽입되었습니다.

이처럼 다중 테이블 INSERT 구문은 여러 개의 INSERT문을 한 문장으로 처리할 수 있기도 하지만, 이와 동시에 여러 개의 테이블에 INSERT를 수행할 수도 있다. 이번에는 ex7_3 뿐만 아니라 ex7_4 테이블에도 데이터를 넣어 보자.

입력

    INSERT ALL
      INTO ex7_3 VALUES (105, '가가가')
      INTO ex7_4 VALUES (105, '나나나')
    SELECT *
      FROM DUAL;

결과

    2개 행 이(가) 삽입되었습니다.

두 테이블의 데이터를 확인해 보면 데이터가 정상적으로 입력된 것을 볼 수 있다.

신간 소식 구독하기

뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.

This article covers the SQL INSERT INTO SELECT statement along with its syntax, examples, and use cases.

In my earlier article SQL SELECT INTO Statement, we explored the following tasks.

• Create a SQL table on the fly while inserting records with appropriate data types
• Use SQL SELECT INTO to insert records in a particular FileGroup
• We cannot use it to insert data in an existing table

We want to insert records as regular database activity. We can insert data directly using client tools such as SSMS, Azure Data Studio or directly from an application. In SQL, we use the SQL INSERT INTO statement to insert records.

The syntax of the INSERT INTO

Once we insert data into the table, we can use the following syntax for our SQL INSERT INTO statement.

If we have specified all column values as per table column orders, we do not need to specify column names. We can directly insert records into the table.

Let us create a sample table and insert data into it.

We can insert data using the following queries. Both queries are valid for data insertion.

We cannot insert data without specifying column names if there is a mismatch between data insertion and the order of column values is different. We can get the following error message.

• Msg 213, Level 16, State 1, Line 6

  Column name or number of supplied values does not match table definition.

• Msg 245, Level 16, State 1, Line 6

  Conversion failed when converting the varchar value ‘raj’ to data type int.

In this example, we’ll use the SQL INSERT INTO statement with supplying values directly in a statement. Suppose we want to insert data from another table. We can still use the SQL INSERT INTO statement with a select statement. Let’s explore this in the next section.

INSERT INTO SELECT Statement Syntax

We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement.

This query performs the following tasks:

• It first Selects records from a table ( Select statement)
• Next, it inserts into a table specified with INSERT INTO

• Note: The Column structure should match between the column returned by SELECT statement and destination table.

INSERT INTO SELECT examples

Example 1: insert data from all columns of source table to destination table

We have the following records in an existing Employee table.

Let us create another table Customers with the following query.

We want to insert all records from the Employees table to the Customers table. We can use the SQL INSERT INTO SELECT statement to do this.

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

In this example, we inserted records for all columns to the Customers table.

Example 2: Insert rows from source to destination table by specifying column names

Let’s drop the existing Customers table before we move forward. Now, we want to create a table with one additional IDENTITY column. IDENTITY column automatically inserts identity values in a table. We also added a City column that allows NULL values

We cannot use the INSERT INTO SELECT statement similar to the above example. If we try to run this code, we get an error message.

In this case, we need to specify the column name with INSERT INTO statement.

In the Customers table, we have an additional column with allows NULL values. Let’s run a Select on Customers table. In the following screenshot, we can see NULL values in the City column.

Suppose you have a different column in the source table. You can still insert records into the destination table with specifying column names in the INSERT INTO SELECT statement. We should have an appropriate data type to insert data. You cannot insert a varchar column data into an INT column.

Add a new column in Employees table using ALTER TABLE statement.

Update the table records with country value India.

Now, rerun the INSERT INTO SELECT statement. You can notice that we are using SELECT * instead of specifying column names.

We get the following error message. This error comes because of the column mismatch between the source table and destination table.

We can map the column between the source and destination table using the following query.

Example 3: Insert top rows using the INSERT INTO SELECT statement

Suppose we want to insert Top N rows from the source table to the destination table. We can use Top clause in the INSERT INTO SELECT statement. In the following query, it inserts the top 1 row from the Employees table to the Customers table.

Example 4: Insert using both columns and defined values in the SQL INSERT INTO SELECT Statement

In previous examples, we either specified specific values in the INSERT INTO statement or used INSERT INTO SELECT to get records from the source table and insert it into the destination table.

We can combine both columns and defined values in the SQL INSERT INTO SELECT statement.

We have the following columns in the Customers and Employees table. Previously, we did not insert any values for the City column. We do not have the required values in the Employee table as well. We need to specify an explicit value for the City column.

In the following query, we specified a value for the City column while the rest of the values we inserted from the Employees table.

In the following query, we can see it inserts one row (due to Top (1) clause) along with value for the City column.

Example 5: INSERT INTO SELECT statement with Join clause to get data from multiple tables

We can use a JOIN clause to get data from multiple tables. These tables are joined with conditions specified with the ON clause. Suppose we want to get data from multiple tables and insert into a table.

In this example, I am using AdventureWorks2017 database. First, create a new table with appropriate data types.

This table should contain records from the output of a multiple table join query. Execute the following query to insert data into HumanResources.EmployeeData table.

Example 6: INSERT INTO SELECT statement with common table expression

We use Common Table Expressions (CTE) to simplify complex join from multiple columns. In the previous example, we used JOINS in a Select statement for inserting data into a SQL table. In this part, we will rewrite the query with CTE.

In a CTE, we can divide code into two parts.

1. We define CTE by a WITH clause before SELECT, INSERT, UPDATE, DELETE statement
2. Once we define CTE, we can take reference the CTE similar to a relational SQL table

Execute the following code to insert data using a CTE.

Example 7: INSERT INTO SELECT statement with a Table variable

We use Table variablessimilarly to a temporary table. We can declare them using the table data type. This table can be used to perform activities in SQL Server where we do not require a permanent table. You can divide the following query into three parts.

1. Create a SQL Table variable with appropriate column data types. We need to use data type TABLE for table variable
2. Execute a INSERT INTO SELECT statement to insert data into a table variable
3. View the table variable result set

Conclusion

In this article, we explore the use cases of the INSERT INTO SELECT statement. I hope you found this article helpful. Feel free to provide feedback in the comments below.

• Author
• Recent Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at

View all posts by Rajendra Gupta