How can I select the newest 40 records into a temp table?

Question

I would like to get the latest 40 records into a temp table, something like this:

SELECT * INTO #MY_TEMP
FROM
  (
    SELECT TOP 40 *
    FROM SOME_TABLE
    ORDER BY RECORD_DATE DESC
  )

However I am getting an error: An ORDER BY clause is not allowed in a derived table.

I saw a few workarounds mentioned on other postings, involving TOP PERCENT, but my select already uses a TOP and it is not working.

How can I get these records into my temp table?

Answer 1

You can not use ORDER BY in subselect statements. Instead remove the subselect. Your SQL should look like this:

SELECT TOP 40 * INTO #MY_TEMP
FROM SOME_TABLE
ORDER BY RECORD_DATE DESC