CODEWITHSUNDEEP
sqlsql-serverssisoledb
el_bastard0
el_bastard0

Reputation: 55

SSIS Multiple insert statements in OLE DB Command

When I try to put more than one insert Statement in a OLE DB Command like this:

    INSERT INTO [TABLE_A] ([NAME_A]) VALUES (@a)
    INSERT INTO [RELATION_TABLE]([ID_A],[ID_B])
    VALUES (
        (SELECT [ID_A] FROM [TABLE_A] WHERE [NAME_A] = @a),
        (SELECT [ID_B] FROM [TABLE_B] WHERE [NAME_B] = @b)
    )

I get this error:

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.".

The sub-select statements return only one record each. The variables have been declared and set. Any suggestions how to solve this or where the error exactly is?

Upvotes: 1

Views: 432

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would suggest running this query:

SELECT a.ID_A, b.ID_B
FROM TABLE_A a JOIN
     TABLE_B b
     ON a.NAME_A = @a AND b.NAME_B = @b;

This is likely to return duplicates -- indicating that you are wrong about the data. There are duplicates. You should probably fix the data and create a unique index or constraint on name in each table to ensure data integrity.

If this returns what you want, then phrase the insert as:

INSERT INTO [RELATION_TABLE]([ID_A],[ID_B])
    SELECT a.ID_A, b.ID_B
    FROM TABLE_A a JOIN
         TABLE_B b
         ON a.NAME_A = @a AND b.NAME_B = @b;

Upvotes: 1

Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Make sure the select statements return only one value.

SELECT [ID_A] FROM [TABLE_A] WHERE [NAME_A] = @a
SELECT [ID_B] FROM [TABLE_B] WHERE [NAME_B] = @b

Upvotes: 0

Related Questions