CODEWITHSUNDEEP
sqlsql-server
user122222
user122222

Reputation: 2429

Change multiple ids to table variable sql

I have sql query in which I am inserting multiple records based on how many Ids there are (few hundred):

INSERT INTO dbo.ChangeBook (ID_Part, Price, ID_Client)
    SELECT d.ID_Parties, d.Price, a.TagId
    FROM dbo.Audit a CROSS JOIN
         (VALUES (1, 0.5), (3, 0.5), (5, 0.5), (6, 0.5), (8, 0.5), (9, 0.5)
         ) d(ID_Parties, Price)
    WHERE ID_Client in (2354, 4387, 5188, 5550, 1169, n+1);

this (2354, 4387, 5188, 5550, 1169, n+1) comes from manually copying ids from different select, let's say: SELECT TOP 10000 a.TagId FROM dbo.Audit

so what I tried to do was to declare a table variable type so sql becomes:

DECLARE @Ids TABLE(Id INTEGER);

INSERT INTO @Ids SELECT TOP 10000 a.TagId FROM dbo.Audit
    
INSERT INTO dbo.ChangeBook (ID_Part, Price, ID_Client)
    SELECT d.ID_Parties, d.Price, a.TagId
    FROM dbo.Audit a CROSS JOIN
         (VALUES (1, 0.5), (3, 0.5), (5, 0.5), (6, 0.5), (8, 0.5), (9, 0.5)
         ) d(ID_Parties, Price)
    WHERE ID_Client in (2354, 4387, 5188, 5550, 1169, n+1);

but how do I then use @Ids instead of 2354, 4387, 5188, 5550, 1169, n+1?

Upvotes: 0

Views: 107

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12969

you need to use a.TagId as ID_Client only exists in your target table.

INSERT INTO dbo.ChangeBook (ID_Part, Price, ID_Client)
    SELECT d.ID_Parties, d.Price, a.TagId
    FROM dbo.Audit a CROSS JOIN
         (VALUES (1, 0.5), (3, 0.5), (5, 0.5), (6, 0.5), (8, 0.5), (9, 0.5)
         ) d(ID_Parties, Price)
    WHERE a.TagId in (SELECT Id FROM @Ids );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Just use in (select . . ):

WHERE ID_Client IN (SELECT i.id FROM @ids i)

Upvotes: 3

Related Questions