But cannot seem to be able to convert it to an Insert.
The below outputs the fields I need in the order I need them into a new table "temp2". However, I am trying to append them to an existing table "Indices_Data" rather than creating a new table "temp2".
SELECT * INTO temp2 FROM (SELECT TOP 60 [Date],[Security],[Close],[High],[Low],[Return], [CompoundReturn],[High]-[Low] as [R1],[R1]/(([High] + [Low])*.05) as R2, [Return]^2 as ReturnSqrd,[CompoundReturn]^2 as CompoundReturnSqrd FROM Indices_WS where Security ='BEL20 Index' Order by [Date] Desc) AS temp ORDER BY temp.Date;
Thanks in advance.
Not a Duplicate..I have looked at this and some other comments on what appear to be similar issues. The complication comes Insert into with the use of Select "Top" and using it on Dates requiring order by to be desc to get the most recent records...{making that "temp"} then changing the sort by to asc
Specify the fields you want to insert data into. Use SELECT to pull the data.
INSERT INTO Indices_Data([Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [R1], [R2], ReturnSqrd, CompoundReturnSqrd) SELECT TOP 60 [Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [High]-[Low] As [R1], [R1]/(([High] + [Low])*.05) As R2, [Return]^2 As ReturnSqrd, [CompoundReturn]^2 As CompoundReturnSqrd FROM Indices_WS where Security ='BEL20 Index' ORDER BY [Date] Desc;
If you must save in ASC order:
INSERT INTO Indices_Data([Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [R1], [R2], ReturnSqrd, CompoundReturnSqrd) SELECT * FROM (SELECT TOP 60 [Date], [Security], [Close], [High], [Low], [Return], [CompoundReturn], [High]-[Low] As [R1], [R1]/(([High] + [Low])*.05) As R2, [Return]^2 As ReturnSqrd, [CompoundReturn]^2 As CompoundReturnSqrd FROM Indices_WS where Security ='BEL20 Index' ORDER BY [Date] Desc) AS T ORDER BY [Date] ASC;
Saving calculated (data dependent on other data) values is usually a bad design. Do the calcs when needed.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments