0

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

Hedgebox
  • 159
  • 2
  • 11
  • 1
    Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) – June7 Mar 04 '18 at 00:31
  • The solution is still an INSERT SELECT. The twist of saving ASC is minor adjustment. – June7 Mar 04 '18 at 18:46

1 Answers1

1

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.

June7
  • 19,874
  • 8
  • 24
  • 34
  • The above Insert does not work. 1st thing is there are not enough closing brackets...2 opening 1 after Data & 1 Before Select but only 1 after Desc. Also...the "as temp" Order by temp.[Date] asc" allows the data to go into the table in Asc order after the "Top" in desc order gets the "most current 60 records. thanks for the attempt. – Hedgebox Mar 04 '18 at 02:20
  • Edited to fix my typos. The syntax works for me. https://www.w3schools.com/SQL/sql_insert_into_select.asp – June7 Mar 04 '18 at 07:52
  • Again many thanks...I have seen the w3schools page and the edits do allow for the insert to run, however, when using Top on dates to get the most recent you need the sort to be Desc...I am attempting to put them into the Table asc...so you need to create a way using the results of the Select Top to then change the sort to Asc by date hence the code...section below... – Hedgebox Mar 04 '18 at 08:37
  • (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 – Hedgebox Mar 04 '18 at 08:38
  • Here is a link to the reversing of the sort by creating the "alias"...https://stackoverflow.com/questions/2572496/select-top-n-records-ordered-by-x-but-have-results-in-reverse-order. If you run the Select Into code pasted above you will see does create the data in Asc order on the temp table... – Hedgebox Mar 04 '18 at 08:41