CODEWITHSUNDEEP
sql-servert-sql
JimBoy
JimBoy

Reputation: 597

Write result of query into a new table

How can I write the result of the SQL Server query, displayed in this question : Transform a table from rows to columns, into a new table?

I tried the following, but it isn't working:

SELECT * INTO [dbo.table] FROM query;

Upvotes: 1

Views: 157

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Multipart name schema_name.table_name:

SELECT * 
INTO [dbo].[new_table_name] 
FROM query;

For your case you need to use:

SET @query = N'SELECT Name, ' + @cols + ' 
            INTO [dbo].[new_table_name]
            from 
            (
                select Name, Product,
                [rn] = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Product)
                from #mytable
            ) x
            pivot 
            (
                max(Product)
                for rn in (' + @cols_piv + ')
            ) p ';

EXEC [dbo].[sp_executesql] @query;

SELECT *
FROM [dbo].[new_table_name];

Keep in mind that if table exists you need first to DROP it:

DROP TABLE [dbo].[new_table_name];

The problem with your approach is that dynamic PIVOT can return variable length columns. So the next call will be different and you need to recreate table every time.

Upvotes: 1

Christian Phillips
Christian Phillips

Reputation: 18749

If you want to add the data to an existing table, you will need to use...

Insert Into dbo.Table (column1, column2) 
SELECT Column1, Column2 FROM query

However, if youre looking to use a Temp Table, you will need...

SELECT * INTO #table FROM query;

There's a comprehensive article here on using Temp Tables etc

Upvotes: 0

Related Questions