SQL Insert-Select Statement

Question

I use Visual Basic 6.0 with SQL Server 2005

Here is my code :

Cn.Execute "INSERT INTO schedule (sch_name, st_id, sch_note)
            SELECT '" & txtSchedname.Text & "', st_id, '" & txtNote.Text & "'
            FROM scheduletype
            WHERE st_name = '" & cboSchedtype.Text & "'"

This is an insert into select statement and works fine. Two inputs directly saved into the [schedule] table and one input coming from [scheduletype] table.

But what if there is no matching records for cboSchedtype.Text?

SELECT st_id
FROM scheduletype
WHERE st_name = '" & cboSchedtype.Text & "'"

Here's I want to do :

I. Make a 'sub-insert' for the value of cboSchedtype.Text into the [scheduletype] table only if it doesn't exist (before the main insert query does its thing)

II. Otherwise continue normally. (My code successfully does this.)

I think you want to use [MERGE](http://technet.microsoft.com/en-us/library/bb510625.aspx) statement. — ain, Aug 03 '11 at 09:27

Mikael Eriksson · Accepted Answer · 2011-08-04T07:59:43.497

1

Use this to add a scheduletype if it does not exist.

insert into scheduletype 
select 'TheScheduleType'
where not exists (select st_name 
                  from scheduletype
                  where st_name = 'TheScheduleType')

After you have done that you can use your insert statement against schedule because you know that the row will exist.

edited Aug 04 '11 at 07:59

answered Aug 04 '11 at 06:56

Mikael Eriksson

136,425
22
210
281

Now lets concentrate on your first example particularly on the SELECT statement. What if cboSchedtype.Text has no matching values on the [scheduletype] table? If this happens I want the query to insert cboSchedtype.Text into the [scheduletype] table. BTW your query is invalid. Subqueries are not allowed on insert statements. Only scalar expressions are allowed. – ELM Aug 04 '11 at 07:30
@March - Misunderstood your question. I have updated the answer. – Mikael Eriksson Aug 04 '11 at 08:00

score 0 · Answer 2 · answered Aug 04 '11 at 01:55

0

ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx

SELECT st_id FROM scheduletype WHERE st_name = ISNULL('" & cboSchedtype.Text & "', subquery)"

answered Aug 04 '11 at 01:55

Edgar Velasquez Lim

2,426
18
15

SQL Insert-Select Statement

2 Answers2