Re: Using 'Execute SQL Task' to insert rows

The parse will only tell you that the query is not syntactically rubbish

What the runtime is telling you is that in your subquery you have said
something similar to


select * from dbo.MyTableWHERE Col1 = (select 1 union select 2)

This is bad because you cannot have a value on the left == two values on the
right. If I change this to

select * from dbo.MyTableWHERE Col1 IN (select 1 union select 2)

Then we are back up and working.

Does this help you pinpoint the error?



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Foss" <Foss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71B04E5C-7511-4F85-B156-26A462CD783F@xxxxxxxxxxxxxxxx
> Hi there all,
>
> I'm trying to insert a number of rows into a table using the results of a
> sub-query to populate them.
> They're both on the same connection.
>
> When I parse this, it's fine. When I execute it, I get this message:
> "Error Source : Microsoft OLE DB Provider for SQL Server'
>
> Error Description : The statement has been terminated.
> Subquery returned more than 1 value. This is not permitted when the
> subquery
> follows =, !=, <, <=, >, >= or when the subquery is used as an
> expression."
>
> What am I doing wrong?!
> Here's my SQL statement:
>
> ----------------------------------------
> INSERT INTO dbo.ExportUpdates
> (exup_collisionID, exup_addedat)
> SELECT DISTINCT col_id, GETDATE() AS CurrDate
> FROM dbo.Collisions
> ----------------------------------------
>
> I'm going quite mad looking at this so any help would be much appreciated!
>
> Cheers,
> Foss


.

Relevant Pages

  • RE: Using Execute SQL Task to insert rows
    ... Foss, I read your question and I don't see the subquery in the example you ... Microsoft OLE DB Provider for SQL Server' ... > Here's my SQL statement: ...
    (microsoft.public.sqlserver.dts)
  • Re: update 2 fields problem
    ... as the SQL Server optimizer will usually generate the ... This changes if the subquery has to be duplicated. ... order in which matches are found depends on the execution plan, ... still be affected by the UPDATE statement and the new values for Column1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Incorrect behavior in NOT IN subquery with OPENXML
    ... In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery. ... I've tried to simplify this problem and I've ended up with the sql script ...
    (microsoft.public.sqlserver.xml)
  • Re: Subqueries with parameters
    ... > Crystal Reports. ... I am using SQL Server so I can do it with a stored ... > clauses or to put parameters on both the subquery and the main query. ... > Data Management Solutions LLC ...
    (microsoft.public.vb.crystal)