Putting many tables in to one table in SQL Server?

Question

I'm trying to insert the result from a query into a new table.

I'm using this query and want to gather the result into a single table.

The query (I found somewhere) looks like this:

USE [AdventureWorksDW2012]

SELECT 
    OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
    T.[name] AS [table_name], 
    AC.[name] AS [column_name],
    TY.[name] AS system_data_type, 
    AC.[max_length],
    AC.[precision], AC.[scale], 
    AC.[is_nullable], AC.[is_ansi_padded]
FROM 
    sys.[tables] AS T
INNER JOIN 
    sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN 
    sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] 
                   AND AC.[user_type_id] = TY.[user_type_id]
WHERE 
    T.[is_ms_shipped] = 0
ORDER BY 
    T.[name], AC.[column_id];

well firstly, there is no `insert` statement in that SQL, generally you'll write `insert into....` or `select col1, col2 into` — Tanner, Jan 19 '17 at 12:02
Possible duplicate of [SQL Server SELECT into existing table](http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table) — Tanner, Jan 19 '17 at 12:02

score 0 · Answer 1 · answered Jan 19 '17 at 12:02

Use INTO clause as next:-

USE [AdventureWorksDW2012]
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
Into New_table -- this line is added.
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]

The sample code is

Select *
Into New_table 
From Exist_Table

and as MSDN says:-

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

score 0 · Accepted Answer · answered Jan 19 '17 at 12:02

Try using an INTO clause like this:

USE [AdventureWorksDW2012]
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
INTO dbo.MyNewTable
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]   
                             AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]
;

Putting many tables in to one table in SQL Server?

2 Answers2