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 ','

Answers


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.


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%'

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


Need Your Help

Stringbuilder maximum length

android

I was wondering what is the maximum capacity of string builder (or stringbuffer)

dependency management with maven

java maven-2 dependencies

I have lately become a big fan of Maven for controlling the build cycle for my application. However I've encountered some rough edges with Maven's dependency management. I'm wondering if these are

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.