How Can Insert Multi Rows in a table using array

Question

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 ?

What you have there doesn't make sense as it stands. What are you expecting to INSERT here? A cartesian product of every id from Files and Products (that meet the requisite of the WHERE)? — Thom A
– Thom A ♦, Commented Jun 3, 2019 at 8:04
Use While loop to fetch each record and insert to your target table — Sreenu131
– Sreenu131, Commented Jun 3, 2019 at 8:05
@Sreenu131 no, don't use a WHILE loop, that is awfully slow. SQL Server excels as Set Based methods, not iterative ones. I suggest against using that methodology Farshad Razaghi. — Thom A
– Thom A ♦, Commented Jun 3, 2019 at 8:06
@Larnu I have some products that has many files; an a table that connect them together. some files are same for some products — Farshad Razaghi
– Farshad Razaghi, Commented Jun 3, 2019 at 8:06
So what links Files and Products? Don't forget, we can't see your data, and we don't have any samples or expected results. Best I can suggest at the moment is you want INSERT INTO...SELECT syntax. — Thom A
– Thom A ♦, Commented Jun 3, 2019 at 8:07

Thom A · Accepted Answer · 2019-06-03 08:10:23Z

1

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.

answered Jun 3, 2019 at 8:10

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How Can Insert Multi Rows in a table using array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related