Inserting multiple rows in one query about querybuilder HOT 17 CLOSED

@PejmanNik can you submit a pull request for it?

from querybuilder.

yes you are right, adding multiple component is a better option 👍

from querybuilder.

Hello nice idea!, but I think it would be better if the insert method will have an overload that takes a list of dictionaries, since the insert is an action method (where it should execute and finalize the query), in the coming version the insert method will execute the query and insert the records in the db, this why I've introduced the AsInsert method (still in dev branch)

from querybuilder.

Hi
Is there any progress on AsInsert overload for accepting multi-row insert?
can I help? like Laravel we can accept an array as parameter

from querybuilder.

@ahmad-moussawi Yes of course

from querybuilder.

That's great am ready to merge when ready

from querybuilder.

@ahmad-moussawi Thanks.
You prefer to add multiple insert component to the query or change InsertClause class?
I suggest adding multiple insert component to the query.

from querybuilder.

Thanks, I am working on it.

from querybuilder.

docs updated, thanks for your support
https://sqlkata.com/docs/update#insert-many
https://sqlkata.com/docs/execution/update#insert-multiple-record

from querybuilder.

Great 👍
Thank you for this awesome project

from querybuilder.

@ahmad-moussawi What do you think about to accept query object in the list for support subquery in inserting multi-record?

from querybuilder.

@PejmanNik sorry I didn't catch it, do you mean passing multiple queries on insert ?

from querybuilder.

Thank you, Ahmad.
No. I mean something like this:

var cols = new [] {"Name", "Price"};
var subQuery = new Query("Price").Select("Amount").Limit(1);

var data = new [] {
    new object[] { "A", subQuery.Clone().Where("Name","A") },
    new object[] { "B", subQuery.Clone().Where("Name","B") },
    new object[] { "C", subQuery.Clone().Where("Name","C") },
};

var query = new Query("Products")
    .AsInsert(cols, data);

from querybuilder.

mm and how it will get translated to SQL ?

from querybuilder.

something like this:

INSERT INTO [Products] ([Name], [Price]) 
VALUES 
 ('A', (select Top 1 Amount from Price where [Name] = 'A') ) 
, ('B', (select Top 1 Amount from Price where [Name] = 'B')),
, ('C', (select Top 1 Amount from Price where [Name] = 'C'))

It can be useful for an insert with value to foreign keys ( get from sub query )

from querybuilder.

I think it's about a very specific case, and maybe developers will get confused about it's usage, a better solution would be generating a single query that retrieve the correct results and using it once.

INSERT INTO [Products] ([Name], [Price])
SELECT [Name], [MaxPrice].[Amount] FROM [Price] JOIN [MaxPrice] ....

anyway thanks for you suggestion :)

from querybuilder.

You are right. I'll think of other ways to do it.
but when we talk about foreign keys (unlike to my example) it needs subquery (multi tiple fields from multi tiple table).

Thank you.

from querybuilder.