CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Use two queries to populate table with INSERT statement

Could someone elucidate for me why the following does not work:

insert into DrugInteractions(ndc_fk, ndc_pk)
   (select top 1 ndc from DrugList where drug_name like 'cipro%'),
   (select top 1 ndc from DrugList where drug_name like 'tizan%')

The column ndc in DrugList is a primary key which uniquely identifies a certain drug. Since you need two things to interact the DrugInteractions table has two copies of the ndc; these two ndcs will be a composite primary key. So say Drug A has an ndc of 1 and Drug B has an ndc of 2, then the row in DrugInteraction would look like:

ndc_pk   ndc_fk
1        2

Is there a way to populate a table using an insert statement with two queries, one for each column like I'm trying? The error I get is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','

Upvotes: 1

Views: 672

Answers (3)

vvvv4d
vvvv4d

Reputation: 4095

insert into DrugInteractions(ndc_fk, ndc_pk)
   select top 1 ndc, newid() from DrugList where drug_name like 'cipro%'
   union
   select top 1 ndc, newid() from DrugList where drug_name like 'tizan%'

Upvotes: 4

sgeddes
sgeddes

Reputation: 62831

Here's an alternative to running multiple select statements:

insert into DrugInteractions (ndc_fk, ndc_pk) 
select min(case when drug_name like 'cipro%' then ndc end),
  min(case when drug_name like 'tizan%' then ndc end)
from DrugList

SQL Fiddle Demo

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

You need to use VALUES to combine them;

insert into DrugInteractions(ndc_fk,ndc_pk)
VALUES(
  (select top 1 ndc from DrugList where drug_name like 'cipro%'),
  (select top 1 ndc from DrugList where drug_name like 'tizan%')
)

An SQLfiddle to test with.

Upvotes: 2

Related Questions