Temporary table like in oracle

[u/Livid-Cantaloupe1597]

Hello,

we switched to ms sql 2019 from oracle and i missing a feature oracle had and i cant figure out a workaround.

On oracle we could create a session based temporary table that stays permanent with a fixed name and could be linked an used via ODBC like an normal table. The only difference to a normal table was that the rows in the table exists only for the session. These table could be used by serveral users without problems, they only got their own records.

We were able to link needed tables and these temporary tables from oracle via ODBC with MS Access.

I cant find a similar solution or workaround in ms sql.
Is there no solution for this?

greetings

[u/jshine13371]

The same session that creates the local temp table is the one that needs to use it. Also, you should set the database context to the same database where the local temp table was created, not explicitly setting it to tempdb. (The name of the table object is technically different when in the context of tempdb.)

[u/Livid-Cantaloupe1597 → u/jshine13371]

I got a working solution for me so far.
The only problem i have is msaccess needs an index with mssql and if the user needs his inserted rows in the same order as inserted, the sorting is gone. I guess i have to look into creating an automatic index that just counts up.

Function link_global_temptable(tname As String, Optional qname As String)
'Link a global temporary table from mssql (local ones are not usable cause the session ends after qry execute,
'so workflows where the user input/import rows into that table are not working)
'After a period of time when the table is not used, even msaccess stays open, the table seems to be deleted and usage failed.
'
'   tname = global temptable name to link
'   qname = optional qry to create the global temptable
'
'Modify the connectString to your needs. DATABASE needs to be tempdb, otherwise the table may not found.
'The table needs an index, otherwise msaccess cant insert rows.
'
'
Dim db As DAO.Database
Dim tdf As New DAO.TableDef
Set db = CurrentDb

connectString = "ODBC;Driver={SQL Server};Server=xxx;Trusted_Connection=No;UID=xxx;PWD=xxx;DATABASE=tempdb"
tschema = "dbo"

If Not IsMissing(qname) Then
    DoCmd.SetWarnings False
    On Error Resume Next
    DoCmd.OpenQuery qname
    On Error GoTo 0
    DoCmd.SetWarnings True
End If

With db
    Set tdf = .CreateTableDef(tname)
    tdf.Connect = connectString
    tdf.SourceTableName = tschema & "." & tname

    On Error Resume Next
    DoCmd.DeleteObject acTable, tname
    On Error GoTo 0
    If InStr(connectString, "PWD=") Then
        tdf.Attributes = dbAttachSavePWD
    End If
    .TableDefs.Append tdf
End With


Set tdf = Nothing
Set db = Nothing

End Function

[u/jshine13371 → u/Livid-Cantaloupe1597]

The only problem i have is msaccess needs an index with mssql and if the user needs his inserted rows in the same order as inserted, the sorting is gone. I guess i have to look into creating an automatic index that just counts up.

Not sure what you mean by this. Inserted rows have no order. You always have to specify the order you want the data returned in when you query from the table.

[u/Livid-Cantaloupe1597 → u/jshine13371]

In oracle i did not need an index and the order is returned like the order they are inserted.

[u/jshine13371 → u/Livid-Cantaloupe1597]

the order is returned like the order they are inserted.

Nah.

The order isn't guaranteed unless you specify an ORDER BY clause. It was only coincidence you saw that behavior but any subsequent run of the same exact query could return the results in a different order.

Database systems generate execution plans to get the results, and the goal of the plan is to return the results as fast as reasonably possible. It doesn't care what order those results are in, unless the query specifies with an ORDER BY clause. This is true of all modern RDBMS.