Merge temp table as source table

[randomGitBeing132]

Is there a way to use temp table as the source table in merge statement something like this?

await db.mergeInto('mainTable').using('tempData', 'mainTable.itemCode', 'tempData.itemCode').whenMatched()......

SQL Statement
MERGE INTO MainTable AS Target USING #TempData AS Source ON Target.ID = Source.ID WHEN MATCHED THEN UPDATE SET Target.Name = Source.Name WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (Source.ID, Source.Name);

Dialect: MSSQL

[igalklebanov]

Hey 👋

AFAIK, the temporary table has to be defined first, either inside the same SQL procedure/function OR within the same request to database (${query that defines the temp table}; ${merge into query}).

Then you can add the temporary table to database context before invoking the merge query as follows:

db
  .withTables<{
    "#tempData": {
      ID: string;
      itemCode: string | null;
      Name: string;
    };
  }>()
  .mergeInto("MainTable as Target")
  .using("#tempData as Source", "Target.ID", "Source.ID")
  .whenMatched()
  .thenUpdateSet((eb) => ({
    Name: eb.ref("Source.Name"),
  }))
  .whenNotMatched()
  .thenInsertValues((eb) => ({
    ID: eb.ref("Source.ID"),
    Name: eb.ref("Source.Name"),
  }))

https://kyse.link/rIW7a

Lemme know if mssql yells about "#tempData".

[randomGitBeing132]

When trying to insert value into temp table it throws this error

RequestError: Invalid object name '#temp_table'.

await db.schema.createTable('#tempTable').addColumn('testCol', 'bigint').execute();
const tempDb = db.withTables<{
  '#tempTable': {
    testCol: number;
  };
}>();
await tempDb
  .insertInto('#tempTable')
  .values({
    testCol: 1234010001,
  })
  .execute();