[Teach Youself SQL in 10 Minutes] inserting data

SQL数据插入与复制技巧

最新推荐文章于 2017-10-24 08:27:03 发布

原创最新推荐文章于 2017-10-24 08:27:03 发布 · 132 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#DB2

db 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了使用SQL进行数据插入及从一个表复制到另一个表的方法。通过INSERT...SELECT语句可以实现数据过滤插入，而使用SELECT...INTO语句则能够轻松完成表间的数据复制。

1. Inserting Retrieved Data

INSERT INTO Customers(cust_id,

    cust_contact,

    cust_email,

    cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country)

SELECT cust_id,

    cust_contact,

    cust_email,

    cust_name,

    cust_address,

    cust_city,

    cust_state,

    cust_zip,

    cust_country

FROM CustNew;

The SELECT statement used in an INSERT SELECT can include a WHERE clause to filter the data to be inserted.

Column Names in INSERT SELECT This example uses the same column names in both the INSERT and SELECT statements for simplicity's sake. But there is no requirement that the column names match. In fact, the DBMS does not even pay attention to the column names returned by the SELECT. Rather, the column position is used, so the first column in the SELECT (regardless of its name) will be used to populate the first specified table column, and so on.

2. Copying from One Table to Another

SELECT *

INTO CustCopy

FROM Customers;

【Not Supported by DB2 】