Data insert into Microsoft SQL Server

Question

Vanessa 2018-1-5

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/375708-data-insert-into-microsoft-sql-server

回答： prabhat kumar sharma about 15 hours 前

Hello,

I'm trying to insert data into a Microsoft SQL Server. Below is the query:

conn = database('DB', 'user', 'pwd'); colnames = {'"Vessel"','"Filepath"','"Date"','"Review"'};

data=report2; datainsert(conn,'"Table"',colnames,data)

%Close database connection.

close(conn);

The columns are nvarchar type with a set length. When I insert the data blank spaces are padded in the right to the size of the column. How can I avoid that? Is it issue of the odbc driver? Or the database?

Thank you, Vanessa

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

prabhat kumar sharma about 15 hours 前

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/375708-data-insert-into-microsoft-sql-server#answer_1489886

在 MATLAB Online 中打开

Hi Vanessa,

The issue you're encountering with padded blank spaces in nvarchar columns is typically related to how the data is being inserted into the database. This can be influenced by the ODBC driver or the database settings. Here are steps and considerations to address this issue:

Steps to Avoid Padding in nvarchar Columns

Trim Data Before Insert: Ensure that the data you are inserting does not have trailing spaces.
Use Parameterized Queries: This can sometimes help in ensuring that the data is inserted correctly without additional padding.

Here is the refrence code you can follow:

% Establish connection to the SQL Server database
conn = database('DB', 'user', 'pwd', 'Vendor', 'Microsoft SQL Server', 'Server', 'your_server_name', 'PortNumber', 1433, 'AuthType', 'Windows');
% Define the column names
colnames = {'"Vessel"', '"Filepath"', '"Date"', '"Review"'};
% Trim the data to remove trailing spaces
dataTrimmed = cellfun(@strtrim, report2, 'UniformOutput', false);
% Insert the trimmed data
datainsert(conn, '"Table"', colnames, dataTrimmed);
% Close the database connection
close(conn);