如何将选择sql查询的结果转换为ms访问中的新表

时间:2021-09-02 23:32:49

how to convert result of an select sql query into a new table in msaccess ?

如何将选择sql查询的结果转换为msaccess中的新表?

5 个解决方案

#1


12  

You can use sub queries

您可以使用子查询

SELECT a,b,c INTO NewTable 
FROM (SELECT a,b,c
FROM TheTable
WHERE a Is Null)

#2


9  

Like so:

像这样:

SELECT    *
INTO      NewTable
FROM      OldTable

#3


3  

First, create a table with the required keys, constraints, domain checking, references, etc. Then use an INSERT INTO..SELECT construct to populate it.

首先,创建一个包含所需键,约束,域检查,引用等的表。然后使用INSERT INTO..SELECT构造来填充它。

Do not be tempted by SELECT..INTO..FROM constructs. The resulting table will have no keys, therefore will not actually be a table at all. Better to start with a proper table then add the data e.g. it will be easier to trap bad data.

不要被SELECT..INTO..FROM结构诱惑。结果表将没有键,因此根本不会是一个表。最好从正确的表开始,然后添加数据,例如陷阱坏数据会更容易。

For an example of how things can go wrong with an SELECT..INTO clause: it can result in a column that includes the NULL value and while after the event you can change the column to NOT NULL the engine will not replace the NULLs, therefore you will end up with a NOT NULL column containing NULLs!

有关SELECT..INTO子句可能出错的示例:它可能导致包含NULL值的列,而在事件之后您可以将列更改为NOT NULL,引擎将不会替换NULL,因此你最终会得到一个包含NULL的NOT NULL列!

Also consider creating a 'viewed' table e.g. using CREATE VIEW SQL DDL rather than a base table.

另请考虑创建“已查看”表格,例如使用CREATE VIEW SQL DDL而不是基表。

#4


1  

If you want to do it through the user interface, you can also:

如果要通过用户界面执行此操作,还可以:

A) Create and test the select query. Save it.

A)创建并测试选择查询。保存。

B) Create a make table query. When asked what tables to show, select the query tab and your saved query.

B)创建一个make表查询。当询问要显示的表格时,请选择查询选项卡和保存的查询。

C) Tell it the name of the table you want to create.

C)告诉它你想要创建的表的名称。

D) Go make coffee (depending on taste and size of table)

D)去煮咖啡(取决于餐桌的味道和大小)

#5


0  

Select *
Into newtable
From somequery

#1


12  

You can use sub queries

您可以使用子查询

SELECT a,b,c INTO NewTable 
FROM (SELECT a,b,c
FROM TheTable
WHERE a Is Null)

#2


9  

Like so:

像这样:

SELECT    *
INTO      NewTable
FROM      OldTable

#3


3  

First, create a table with the required keys, constraints, domain checking, references, etc. Then use an INSERT INTO..SELECT construct to populate it.

首先,创建一个包含所需键,约束,域检查,引用等的表。然后使用INSERT INTO..SELECT构造来填充它。

Do not be tempted by SELECT..INTO..FROM constructs. The resulting table will have no keys, therefore will not actually be a table at all. Better to start with a proper table then add the data e.g. it will be easier to trap bad data.

不要被SELECT..INTO..FROM结构诱惑。结果表将没有键,因此根本不会是一个表。最好从正确的表开始,然后添加数据,例如陷阱坏数据会更容易。

For an example of how things can go wrong with an SELECT..INTO clause: it can result in a column that includes the NULL value and while after the event you can change the column to NOT NULL the engine will not replace the NULLs, therefore you will end up with a NOT NULL column containing NULLs!

有关SELECT..INTO子句可能出错的示例:它可能导致包含NULL值的列,而在事件之后您可以将列更改为NOT NULL,引擎将不会替换NULL,因此你最终会得到一个包含NULL的NOT NULL列!

Also consider creating a 'viewed' table e.g. using CREATE VIEW SQL DDL rather than a base table.

另请考虑创建“已查看”表格,例如使用CREATE VIEW SQL DDL而不是基表。

#4


1  

If you want to do it through the user interface, you can also:

如果要通过用户界面执行此操作,还可以:

A) Create and test the select query. Save it.

A)创建并测试选择查询。保存。

B) Create a make table query. When asked what tables to show, select the query tab and your saved query.

B)创建一个make表查询。当询问要显示的表格时,请选择查询选项卡和保存的查询。

C) Tell it the name of the table you want to create.

C)告诉它你想要创建的表的名称。

D) Go make coffee (depending on taste and size of table)

D)去煮咖啡(取决于餐桌的味道和大小)

#5


0  

Select *
Into newtable
From somequery