insert table1 select from table2,table3 where

Question

I am using SQL Server 2008 R2 on Windows 7, and I am trying to fill in the missing rows in a table. For background, I have

a set of "parameters" which all have a (string) value. There are about 200 of these parameters
a set of "context"s where I need to have a value for every parameter. There are dozens of these contexts.

I am modelling this in the database with three tables like this:

[Parameter] (
    [ID] int not null,
    [DefaultValue] varchar(100) not null
)

[Context] (
    [ID] int not null
)

[ParameterValues] (
    [ID] int IDENTITY(1,1) not null,
    [ParameterID] int not null,
    [ContextID] int not null,
    [Value] varchar(100) not null
)

So there should be a row in the parameter values table for every combination of context and parameter. So for 200 parameters and 10 contexts, I should expect to have 200x10 = 2000 rows in the ParameterValues table.

For historical reasons, we have some parameter values missing for some contexts, and I want to fill in all the missing values in the ParameterValues table.

I am trying to use the following query:

insert [ParameterValues]
  select [Parameter].[ID], [Context].[ID], [Parameter].[DefaultValue]
    from [Parameter],[Context]
     where not exists 
      (select 1 from [ParameterValues]
                where [ParameterValues].[ContextID] = [Context].[ID] 
                  and [ParameterValues].[ParameterID] = [Parameter.[ID])

I keep getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I guess I am just not understanding the limitations of the insert...select syntax etc. Any suggestions for getting this correct would be greatly appreciated.

Update:

If I just run the "select ... from ... where not exists..." then I get the correct number of rows showing up (I know how many are missing in each context). I have checked those rows and they seem to have the correct values and there are no duplicates.

If I do the select into a temp table, it works fine, and I get the expected contents in that temp table. But I can't then do:

insert [ParameterValues] select * from #temptable

because that gives me the same error. So there must be something in the structure or FKs in my parameter values table that breaks this.

Using SSMS, I can copy and paste those rows directly into the target table and get no errors, so the values in those rows look fine, and don't break any FKs etc. I just checked again and there are no triggers or SPs in this database at all, and the FK relationships look OK.

I have gone round the houses on this for the last several hours, and it is driving me nuts. In the short term, I have the data that I need to work with, but I will have to repeat this process on other copies of the DB and would like to do it in a standard repeatable way via scripts etc, rather than manual hacks...

You shoud NEVER use an implicit join, one reason why you are having trouble is because you are not correctly joining here, so you have an accidental cross join. VERY BAD to use implict joins. It is irresponsible to use them in 21st century code since they were replaced in the last century for goodness sakes. — HLGEM, Mar 27 '15 at 17:54
Please explain. Rather than just criticise, try to give a good solution that might help. — TimChippingtonDerrick, Mar 28 '15 at 14:20

Andrey Korneyev · Answer 1 · 2015-03-27T15:39:00.903

1

You can do it like this:

insert into ParameterValues (ParameterID, ContextID, Value)
select P.ID, C.ID, P.DefaultValue
from Parameter as P, Context as C
where not exists 
      (select top 1 * from ParameterValues as PV
       where PV.ContextID = C.ID and PV.ParameterID = P.ID)

UPDATE

Frankly speaking, your original query should work too (despite query using table aliases looks more elegant from my point of view, and it is always better to explicitly specify column names you're inserting into). And after some thoughts I can't find a reason for your query to throw such an exception.

Are you sure you've provided your actual query you're having problems with?

edited Mar 27 '15 at 15:39

answered Mar 27 '15 at 15:20

Andrey Korneyev

26,353
15
70
71

Initially I thought it was because he didn't specify columns on the insert, but after running his query, it worked for me too. – DerekCate Mar 27 '15 at 15:37
Thanks for the input. The query *is* simplified, as are the table structures. The full tables have another 5-10 columns each, and the query is a bit messier. Just tried the same again in a dummy database and it works if I don't also define the foreign keys etc. So I may have some clues.... – TimChippingtonDerrick Mar 27 '15 at 16:09
Using implict joins is a SQL antipattern. – HLGEM Mar 27 '15 at 17:52

insert table1 select from table2,table3 where

1 Answers1