How to Create a Table from Select Query Result in SQL Server 2008

How to create a table from select query result in SQL Server 2008

Use following syntax to create new table from old table in SQL server 2008

Select * into new_table  from  old_table 

Try to create a table from Select - SqL Server 2008 throws error

How about:

SELECT * into JmxMonSer FROM services WHERE monitoring_enabled=1

If the table already exists (and the columns types and ordering line up), you can use:

INSERT INTO JmxMonSer SELECT * FROM services WHERE monitoring_enabled=1

How to create table using select query in SQL Server?

An example statement that uses a sub-select :

select * into MyNewTable
from
(
select 
  * 
from 
[SomeOtherTablename]
where 
  EventStartDatetime >= '01/JAN/2018' 
)
) mysourcedata
;

note that the sub query must be given a name .. any name .. e.g. above example gives the subquery a name of mysourcedata. Without this a syntax error is issued in SQL*server 2012.

The database should reply with a message like:
(9999 row(s) affected)

Create a table from a Select query

I did it dynamically, try this code: Column names will become tablename + column name so no column name will be duplicated.

DECLARE @strColumns varchar(max) = ''

SELECT @strColumns = COALESCE(@strColumns + ',', '') + A.TableName + '.' + QuoteName(Column_Name) + ' AS ' + A.Alies
FROM (
SELECT Column_Name, 'Agente' TableName, 'Agente_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Agente' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Cte' TableName, 'Cte_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Cte' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'CteEnviarA' TableName, 'CteEnviarA_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'CteEnviarA' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Unidad' TableName, 'Unidad_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Unidad' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Venta' TableName, 'Venta_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Venta' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'VentaD' TableName, 'VentaD_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'VentaD' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Art' TableName, 'Art_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Art' AND DATA_TYPE != 'timestamp'
UNION ALL
SELECT Column_Name, 'Alm' TableName, 'Alm_' + QuoteName(Column_Name) AS Alies FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Alm' AND DATA_TYPE != 'timestamp'
) AS A

SELECT @strColumns =SUBSTRING(@strColumns,2, len(@strColumns) - 1)
SELECT @strColumns


EXEC( 'SELECT 
    '+ @strColumns +' INTO ##temp
    FROM dbo.Agente 
    FULL JOIN dbo.Cte ON dbo.Agente.Agente = dbo.Cte.Agente 
    FULL JOIN dbo.CteEnviarA ON dbo.Agente.Agente = dbo.CteEnviarA.Agente 
    FULL JOIN dbo.Unidad ON dbo.CteEnviarA.Unidad = dbo.Unidad.Unidad 
    FULL JOIN dbo.Venta ON dbo.Agente.Agente = dbo.Venta.Agente 
    FULL JOIN dbo.VentaD ON dbo.Agente.Agente = dbo.VentaD.Agente 
    FULL JOIN dbo.Art ON dbo.VentaD.Articulo = dbo.Art.Articulo
    FULL JOIN dbo.Alm ON dbo.Venta.Almacen = dbo.Alm.Almacen')

SELECT * FROM ##temp
DROP TABLE ##temp

Create table as select causes error in SQL Server

SELECT * INTO New_Table 
FROM YourTable

Display Table Name in Query Results; SQL Server 2008

From the comments, it seems that you have multiple tables all with the same structure and similar names This is almost always a sign of poor database design. You should have a single table LOAD_UNISTATS_ACCREDITATION with a column for the year.

If you cannot change the database structure, then perhaps you can create a view:

create view v_LOAD_UNISTATS_ACCREDITATION as
    select lua.*, 2013 as year
    from LOAD_UNISTATS_2013_ACCREDITATION lua
    union all
    select lua.*, 2012 as year
    from LOAD_UNISTATS_2012_ACCREDITATION lua
    . . .;

But the answer to your question is "no". There is no automated way to specify a table name in query. And, for a simple reason. The columns in a query are defined in the SELECT but tables are defined in the FROM. A query can have multiple tables. One could imagine a function such as OBJECT_ID for this purpose, but columns are not first class objects in the database.

get results from queries stored in a table into its own result set?

I am sure there will be cleaner code than this but this should work.
Below query will execute your stored SQLs into a table (#FinalResult) which you can then join to another table (based on ID) to get the final result.

Note: I have used tables from AdventureWorksDW2012 to form my SELECT COUNT queries.

     CREATE TABLE #test
    (
        Query VARCHAR(MAX) ,
        ID INT ,
        Name VARCHAR(100)
    );

INSERT INTO #test (   Query ,
                      ID ,
                      Name
                  )
VALUES (   'select count(*)  FROM dbo.DimAccount' , -- Query - varchar(max)
           1 ,                                      -- ID - int
           'Server01 Logins'                        -- Name - varchar(100)
       ) ,
    (   'select count(*)  FROM dbo.DimCustomer' , -- Query - varchar(max)
        2 ,                                       -- ID - int
        'Server08 Logins'                         -- Name - varchar(100)
    ) ,
    (   'select count(*)  FROM dbo.DimEmployee' , -- Query - varchar(max)
        3 ,                                       -- ID - int
        'Server09 Logins'                         -- Name - varchar(100)
    );



CREATE TABLE #Result
    (
        QueryResult INT --(assuming its only count)
        --,ID INT 
    );

CREATE TABLE #FinalResult
    (
        QueryResult INT --(assuming its only count)
        ,ID INT 
    );

DECLARE @Sql NVARCHAR(MAX);
DECLARE @ID INT 
DECLARE @Count INT
DECLARE ResultCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT Query, ID
    FROM   #test;


OPEN ResultCursor;

FETCH NEXT FROM ResultCursor
INTO @Sql, @id;

WHILE ( @@FETCH_STATUS = 0 )
    BEGIN



        INSERT INTO #Result 
        EXEC sp_executesql @Sql  
        FETCH NEXT FROM ResultCursor
        INTO @Sql, @Id;

        INSERT INTO #FinalResult (   QueryResult ,
                                     ID
                                 )
        SELECT QueryResult, @id FROM #Result

        DELETE FROM #result

    END;

CLOSE ResultCursor;
DEALLOCATE ResultCursor;

SELECT *
FROM   #FinalResult;

DROP TABLE #test;
DROP TABLE #Result;
DROP TABLE #FinalResult;

Create a table with SQL from Query result in SSMS

Try this:

SELECT * 
INTO NEW_TABLE
FROM table1
WHERE code = 'x'
ORDER BY code;

---

Related Topics