Handling Quotation when Inserting Table to SQL Database

I have a table that looks like this:

    id = [1 2]';
    name = {'abc', 'a''bc'}';
    data = table(id,name);
    disp(data)

Now when I tried to insert this table to a MS SQL database, I got this error:

    hdr = {'id','name'};
    insert(conn, 'userName.dbName.tableName', hdr, data)

Apparently it's because of the single quotation mark in the second row of the table, as I can see the first row got inserted successfully.

Thoughts?

[Kojiro Saito]

I cannot reproduce this issue in MATLAB R2018a with SQL Server 2016. Which MATLAB and SQL Server versions are you using?

[Ledger Yu]

Matlab 2013b and SQL Server 2008 R2. Anyway I figured out where the issue lies. Please see my own comment.

Answer 1

[Ledger Yu]

The issue appears to be that when Matlab sends data to SQL, it is already an "interpreted" version. So it was a single quotation that SQL receives, not a double one which I passed as an input to "data".

This runs successfully:

    id = [1 2]';
    name = {'abc', 'a''''bc'}';
    data = table(id,name);
    insert(conn, 'userName.dbName.tableName', hdr, data)