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();

[igalklebanov]

Try using db.connection to reuse the same connection for both queries.

[ShahriarKh]

I want to achieve the same thing (& am using MSSQL) and have the same issue.

I tried:

  await this.db
      .getDb()
      .connection()
      .execute(async db => {
        const a = await db.schema
          .createTable('#temp')
          .addColumn('id', 'integer')
          .execute();

        const b = await db
          .withTables<{
            '#temp': {
              id: number;
            };
          }>()
          .selectFrom('#temp')
          .selectAll()
          .execute();
      });

but got Invalid object name '#temp'.. (this.db.getDb() returns a kysely db instance)

@igalklebanov Am I doing it right? or it's different from what you meant? @randomGitBeing132 Did you find a solution?