CODEWITHSUNDEEP
sqlsql-serverssms
Farshad Razaghi
Farshad Razaghi

Reputation: 49

How Can Insert Multi Rows in a table using array

I need to add multirows at the same time in my sql server Table using this code

declare @idproduct int
declare @idfile int

set @idproduct = (select id from Products where name = 'DR-8416')
set @idfile = (select id from Files where filename like '%8416%')

insert into ProductsFiles(idproducts, idfile) values (@idproduct, @idfile)

that @idfile is an array with many values; when I try to add I recied this error

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

How Can I solve this Problem ?

Upvotes: 0

Views: 66

Answers (1)

Thom A
Thom A

Reputation: 95589

Best I can guess is what you are after is:

INSERT INTO ProductsFiles (idproducts,
                           idfile)
SELECT P.id,
       F.id
FROM Products AS P
     CROSS JOIN Files AS F
WHERE P.[name] = 'DR-8416'
  AND F.[filename] LIKE '%8416%';

Note I have used as CROSS JOIN, as your question suggests there is no relationship between Products and Files. If there is change the CROSS JOIN to an INNER JOIN and add the relevant ON clause. If you don't know about JOIN syntax I suggest looking it up, and learning it. JOIN syntax is one of the fundamentals for SQL and you will not get far without learning it. You will learn far more by taking the time to read up about it than me giving you an answer and trying to explain the basics.

Upvotes: 1

Related Questions