How Can I Create a SQL Server Table from SELECT Query Results?

Creating Tables from SELECT Query Results in SQL Server

SELECT queries are a powerful tool in SQL Server, allowing users to retrieve specific data from tables or views. However, it's often necessary to store these query results permanently in a table. This article will explain how to create a table using the results of a SELECT query.

Query Example

To illustrate the process, let's consider the following sample query:

SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info OPTION (RECOMPILE);

This query retrieves information about the operating system, including its version, service pack level, edition, and language.

Creating a Table from Query Results

To create a table from the query results, use the following syntax:

SELECT <column list> INTO <table name> FROM <source> WHERE <whereclause>

In this case, the column list is the same as the fields in the SELECT query, and the source is the query itself. For example, to create a table named OperatingSystemInfo from the query results, the syntax would be:

SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version 
INTO OperatingSystemInfo 
FROM sys.dm_os_windows_info OPTION (RECOMPILE);

If the table already exists, the INSERT INTO keyword can be used to append the query results to the existing table. The following example would append the results to the OperatingSystemInfo table:

INSERT INTO OperatingSystemInfo (windows_release, windows_service_pack_level, 
       windows_sku, os_language_version) 
SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version 
FROM sys.dm_os_windows_info OPTION (RECOMPILE);

By following these steps, you can easily create tables in SQL Server based on the results of SELECT queries, allowing for the permanent storage and manipulation of the retrieved data.

The above is the detailed content of How Can I Create a SQL Server Table from SELECT Query Results?. For more information, please follow other related articles on the PHP Chinese website!