如果不存在带有参数值的DB2插入行 [英] DB2 insert row if not exist with value from parameter
问题描述
我想在表中插入一行(如果尚不存在)。我有以下直接执行的代码:
I want to insert a row into a table if it doesn't exists yet. I have following code that executes directly:
insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', 2014, '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = 2014)
但是当我按年份将其更改为参数化方法:
However when i change this to a paramtered method by year:
insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', @year, '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = @year)
还尝试了在原子部分中使用声明的变量,但没有成功:
Also tried with a declared variable in an atomic part with no success:
BEGIN ATOMIC
DECLARE varJaar INTEGER;
SET varYear = @year;
insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', varYear , '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = varYear )
END;
我无法执行此操作,因为无法在我的选择部分中使用参数。试图将@year修改为@year作为Year,但是仍然没有成功。
I cannot execute this because i cannot use a parameter in my select part. Tried to modify @year to @year as Year, but still no success.
有没有一种方法可以声明temp变量或在我的选择中使用此参数的东西
Is there a way i can declare a temp variable or something to use this parameter in my select part?
推荐答案
最终通过创建临时表解决了此问题。
变量被临时存储。该表应自动清除,但是在触发我的方法之前,我仍然在try-catch块中执行drop table命令:
Solved this eventually by creating a temp table. The variable is stored temporarily. This table should be cleared automatically however i still perform a drop table command in a try-catch block before triggering my method:
Try
Dim adapter As New DataAdapter()
Dim commandText As String = "drop table QTEMP.tempVariable"
Dim command As DbCommand = adapter.CreateDbCommand(commandText)
adapter.ExecuteNonQuery(command)
Catch notHandledEx As Exception
End Try
create table #tempVariable (year int);
insert into #tempVariable values(@Year);
insert into MyTable (Column1, Column2, Year, Code, Id, UPDATE_IDENT)
select 'Default', 'Default', (select max(year) from #tempVariable), '', 0, 0
from sysibm.sysdummy1
WHERE NOT EXISTS (SELECT * FROM MyTable
WHERE Column1 = 'c1'
AND Column2 = 'c2'
AND Year = @Year)
这篇关于如果不存在带有参数值的DB2插入行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!