轻松上手,快乐学习!

SQL SELECT INTO 语句


SQL SELECT INTO 语句

SELECT INTO 语句将数据从一个表复制到一个新表中。

SELECT INTO语法

将所有列复制到新表中:

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

指定列复制到新表中:

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
 FROM oldtable
WHERE condition;

将使用旧表中定义的列名和类型创建新表。您可以使用AS子句创建新列名。


SQL SELECT INTO 实例

以下SQL语句创建Customers的备份副本:

SELECT * INTO CustomersBackup2017
FROM Customers;

以下SQL语句使用IN子句将表复制到另一个数据库中的新表中:

SELECT *INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

以下SQL语句指定列复制到新表中:

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

以下SQL语句仅将Germany客户复制到新表中:

SELECT *INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

以下SQL语句将多个表中的数据复制到新表中:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN OrdersON Customers.CustomerID = Orders.CustomerID;

提示: SELECT INTO也可用于使用另一个模式创建一个新的空表。只需添加一个WHERE子句,使查询不返回任何数据:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;