Query in insert statement?

Question

I have the following VB statement for inserting text data to a SQL 2008 DB. How can I check if sValueList already exists in sFieldNameList column while doing the insert?

sInsertStatement = "INSERT " & sTableName 
                   & "(" & sFieldNameList & ") VALUES(" 
                   & Mid(sValueList , 3) & ")"

Is it the SQL or VBScript you are having problems with? – D'Arcy Rittich Sep 20 '12 at 16:56 — D'Arcy Rittich, Sep 20 '12 at 16:56

score 1 · Accepted Answer · answered Sep 20 '12 at 16:58

1

I would do it as follows:

"IF NOT EXISTS (SELECT * FROM " & sTableName * " 
                WHERE " & sFieldNameList & " = " & sValueList & ") 
 BEGIN
    "INSERT " & sTableName 
              & "(" & sFieldNameList & ") VALUES(" 
              & Mid(sValueList , 3) & ")"  
 END

(this needs to be adapted to a VBScript syntax though!)

answered Sep 20 '12 at 16:58

Vikdor

23,934
10
61
84

I guess im lost on one thing which maybe I should not be, but tis been a while. in teh if statement, do i execute the objConn.execute as so? IF NOT EXISTS (objConn.execute "SELECT LN FROM " & sTableName * " WHERE LN = " & sValueList(0) & ") – James Ferguson Sep 20 '12 at 17:09
Yeah, that's right. You would pass the entire SQL statement as a single argument to objConn.execute. – Vikdor Sep 20 '12 at 17:14

score 0 · Answer 2 · edited May 23 '17 at 11:55

You need to do either UPDATE or INSERT? What you need is an UPSERT command. Basically you need a WHERE clause like:

where not exists (select 1 from mytable where col2='myId').

Read here how postgres do it.

sql2008 does not have same functionality but check this blog (end of page) where they do:

USE [MyDatabase]
GO
merge into mytable as Target
using mytable2 as Source
on Target.id=Source.id
when matched then 
update set Target.name=Source.name,
Target.Salary = Source.Salary
when not matched then
insert (id,name,salary) values (Source.id,Source.name,Source.Salary);

Query in insert statement?

2 Answers2