Search code examples
elixirphoenix-frameworkecto

How do you insert to a SqlServer table with Ecto?

I'm trying to hook Ecto up to an existing SqlServer database. SqlServer wants to auto-fill the primary key field, so I can't supply a value when I do an insert. I had originally marked the field as required, but even after I removed that, the INSERT query it constructs still has that field and is trying to put :nil into it. So I'm still getting an error. Is there a particular way I'm supposed to set up the model, or something I'm supposed to pass to the INSERT to make it work? How do you insert to SqlServer?

I'm using the tds_ecto adapter. The error I get is:

Cannot insert explicit value for identity column in table 'TextMessageHistory' when IDENTITY_INSERT is set to OFF
Solution

  • Looks like the problem was with how I declared the primary key in my model. I had used:

    @primary_key {:TextMessageHistoryId, :id, []}

    but then it didn't understand that the database would generate the key automatically. So I changed it to:

    @primary_key {:TextMessageHistoryId, :id, autogenerate: true}

    And then it stopped trying to insert things into the primary key.