Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Select Into/Insert with Identity with OVERRIDING SYSTEM VALUE #80

Open
tb-mtg opened this issue Sep 8, 2024 · 0 comments
Open

Select Into/Insert with Identity with OVERRIDING SYSTEM VALUE #80

tb-mtg opened this issue Sep 8, 2024 · 0 comments

Comments

@tb-mtg
Copy link

tb-mtg commented Sep 8, 2024

When I execute the following select into insert:

var q = from p35 in db.Product.IsCompanyCode("S1").IsItemCode("74249")
        from x35 in db.ProductExtension.Where(x=> x.Code == p35.Code).DefaultIfEmpty()
        where x35.Code == null
        select new { p35.Code };

var records = q.Into(db.ProductExtension)
       .Value(x=> x.Id, x=> int.Parse(x.Code))
       .Value(x=> x.Code, x=> x.Code)        
       .Insert();

It generates the SQL below:

INSERT INTO Product(ID, Code)
SELECT Int(p.Code), p.Code
FROM Product
LEFT JOIN ProductExtension x ON x.Code = p.Code
WHERE x.Code IS NULL 

The following exception is thrown

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0798 - Value cannot be specified for GENERATED ALWAYS column ID.

Using OVERRIDING SYSTEM VALUE is supported to override the generated identity value with a custom one.

I originally raised this issue here and it was suggested that:

This is actually provider-specific issue. We have mechanisms to define query hints. iSeries provider just need to implement specific table hint API and add sql generation for it.

Is there any way to add OVERRIDING SYSTEM VALUE to the insert generated SQL statement, with table hints as suggested above?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant