How to create a new table with the results of the select query?

Transact-SQL
1

I created a code to denormalise data and finally went from this:

Column0 Column1
1 Name" "ANGLO AMERICAN - PRICE TO BOOK VAL"
2 "Code" "903076(PTBV)"
3 "Mnemonic" "AAL"
4"2006/12/29" 2,9691
5"2007/01/01" 2,9757
6"2007/01/02" 3,0497
7"2007/01/03" 2,9362
8"2007/01/04" 2,8670
9"2007/01/05" 2,7833
10 2007/01/08" 2,8287
etc...

..to this:

goodcolumn.PNG831×206 8.8 KB

Now I want to convert the obtained result into a new table so I can compute and process the data more easily..

I know this question sounds stupid, but when I use:
SELECT *
INTO NewTable
FROM OldTable

it builds a table with the former 2 columns (1st picture).. how I am supposed to solve this?

Thank you!

2

Your SELECT * needs to be whatever the complex query is that created the result in your Image (.eg. a PIVOT or somesuch), not just a query of the two columns from the original table.

If it helps you can use a wrapper query:

SELECT X.*
INTO NewTable
FROM
(
    SELECT .... complex query ....
    FROM ...
    ...
) AS X
1 Like
3

I am using this code:

SELECT *
INTO Price_to_book_valid
FROM
(
SELECT

select *

from pricetobook

declare @sql nvarchar(max);
declare @fields nvarchar(max);

set @fields=stuff((select ',['+Dateformat1+']'
from pricetobook
where Dateformat1 not in ('Code','Mnemonic')
group by Dateformat1
order by case when Dateformat1='Name' then 0 else 3 end
,Dateformat1
for xml path('')
,type
).value('.','nvarchar(max)')
,1
,1
,''
)
;
print @fields

set @sql='
select '+@fields+'
from (select Dateformat1
,Dataformat2
,sum(rn1*rn2) over(order by rn2) as rn
from (select Dateformat1
,Dataformat2
,case when Dateformat1=''Name'' then 1 else 0 end as rn1
,row_number() over(order by (select null)) as rn2
from pricetobook
) as a
) as a
pivot (max(Dataformat2) for Dateformat1 in ('+@fields+')) as p

'
;

execute sp_executesql @sql;

from pricetobook

) AS X

And when I use your solution it sends me this:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 54
Incorrect syntax near the keyword 'from'.

4

The second half of that is not my solution, that's some completely different Dynamic SQL !!

PRINT the @sql variable before the execute sp_executesql @sql statement and see what it contains. You should be able to, manually, execute that SQL and then debug it and, once fixed, feed back those changes into your Dynamic SQL preparation statement

You've missed off part of my code in the first part. If that is not a typo in your message here then fixing that may help. Suggest you run the first part, separately, until that is debugged before you add on the second, more complex, part.

5

the @sql code works well.. however when I use your code:

          SELECT X.*
           INTO NewTable
           FROM
            (
                 SELECT .... complex query ....
                 FROM ...
                   ...
           ) AS X

It replies me w/ an error..

6

You'll have to post the exact code you have an error with, otherwise i'm just guessing what might be wrong.

If that is what you posted earlier then you can't have DECLARE and multi-statement SET/SELECTS in the middle of a select statement

7

I found another way, with the openquery function