Open randomGitBeing132 opened 2 months ago
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"),
}))
Lemme know if mssql
yells about "#tempData"
.
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();
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