Hi all,
I am not able to add the data from excel file to sql server. Getting this error.
Run command: Column name or number of supplied values does not match table definition.
How to resolve this. Please guide me on this.
How to resolve this. Please guide me on this.
Expand the query and confirm that the values in the INSERT command matches each of the respective field data types.
Hope this helps
@lakshmi.mp Thanks! Are all the fields in the database table String datatype?
@argin.lerit , all the fields are of string type
Does the number of fields in the table match the number of items in the values clause? If this is the case and still errors out, try the format instead of just values:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
@argin.lerit , the columns present in the excel file and sql database table are not the same. What to do in this case.
In that case you need to match them by creating this format:
INSERT INTO table_name (Column1inDatabase, Column2inDatabase, Column3inDatabase, ...)
VALUES (ValueInExcelThatMatchesColumn1inDatabase, ValueInExcelThatMatchesColumn2inDatabase, ValueInExcelThatMatchesColumn2inDatabase, ...);
If you need further help, share the actual fieldnames in the table and the header names in Excel.
Also make sure that if there’s required fields in the table, it might also make the INSERT query fail.
Thanks
Hi @argin.lerit ,
I followed above syntax but getting this error now
Run command: Incorrect syntax near the keyword 'File'.
"insert into PphShopFindingReports (Id, RequestReceivedOn, EngineModel, PrimaryModuleSerialNumber, PrimaryModule2SerialNumber, SecondaryModuleSerialNumber, ReasonForRemovalBW, PrimaryModuleWorkScopeFinal, PrimaryModule2WorkScopeFinal, SecondaryModuleWorkScopeFinal, PrimaryModuleTSN, PrimaryModuleCSN, PrimaryModuleTSO, PrimaryModuleCSO, PrimaryModuleTSHSI, PrimaryModuleTSLV, PrimaryModuleCSLV, PrimaryModule2TSN, PrimaryModule2CSN, PrimaryModule2TSO, PrimaryModule2CSO, PrimaryModule2TSHSI, PrimaryModule2TSLSV, SecondaryModuleTSN, SecondaryModuleCSN, SecondaryModuleTSO, SecondaryModuleTSLR, SecondaryModuleTSLV, HSICategory, PrimaryModuleOVHCategory, PrimaryModule2OVHCategory, SecondaryModuleOVHCategory, ProdSapUpdate, StagSapUpdate, SDOrder, ReasonForRemovalAdditionalInfo, SBIncorpDuringShopVisit, DateOfRemoval, FileName, Status, ReplaceWorkScope, StagUpdate, ProdUpdate, File, StatusId, Description, ProcessTime, RequestUserId, IsLocked, LockedTime, ShopEventDateRecieved, Discrepancy, EngineMarket, ) values (' ',' ','"+APS1+"',' ',' ',' ','"+Reason_remove+"','"+power_sec+"','"+Pri_load+"','"+Sec_gear+"','"+tt1+"','"+tslv1+"',' ',' ',' ','"+tsr2+"','"+cslv1+"',' ', ' ', ' ', ' ', ' ', ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','"+ SD1+"','"+add_info_rm+"','"+SB_incorp1+"','"+str2+"','"+filename+"',' ',' ',' ',' ', ' ',' ', ' ', ' ', ' ', ' ', ' ', '"+str1+"', ' ', ' ')"
Please look into it.
File is a reserved word in SQL and is causing the issue since it’s also a column name in the table:
Enclose it in [] like this
insert into PphShopFindingReports (Id, RequestReceivedOn, EngineModel, PrimaryModuleSerialNumber, PrimaryModule2SerialNumber, SecondaryModuleSerialNumber, ReasonForRemovalBW, PrimaryModuleWorkScopeFinal, PrimaryModule2WorkScopeFinal, SecondaryModuleWorkScopeFinal, PrimaryModuleTSN, PrimaryModuleCSN, PrimaryModuleTSO, PrimaryModuleCSO, PrimaryModuleTSHSI, PrimaryModuleTSLV, PrimaryModuleCSLV, PrimaryModule2TSN, PrimaryModule2CSN, PrimaryModule2TSO, PrimaryModule2CSO, PrimaryModule2TSHSI, PrimaryModule2TSLSV, SecondaryModuleTSN, SecondaryModuleCSN, SecondaryModuleTSO, SecondaryModuleTSLR, SecondaryModuleTSLV, HSICategory, PrimaryModuleOVHCategory, PrimaryModule2OVHCategory, SecondaryModuleOVHCategory, ProdSapUpdate, StagSapUpdate, SDOrder, ReasonForRemovalAdditionalInfo, SBIncorpDuringShopVisit, DateOfRemoval, FileName, Status, ReplaceWorkScope, StagUpdate, ProdUpdate, [File], StatusId, Description, ProcessTime, RequestUserId, IsLocked, LockedTime, ShopEventDateRecieved, Discrepancy, EngineMarket, ) values (' ',' ','"+APS1+"',' ',' ',' ','"+Reason_remove+"','"+power_sec+"','"+Pri_load+"','"+Sec_gear+"','"+tt1+"','"+tslv1+"',' ',' ',' ','"+tsr2+"','"+cslv1+"',' ', ' ', ' ', ' ', ' ', ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','"+ SD1+"','"+add_info_rm+"','"+SB_incorp1+"','"+str2+"','"+filename+"',' ',' ',' ',' ', ' ',' ', ' ', ' ', ' ', ' ', ' ', '"+str1+"', ' ', ' ')
Hope this helps!
@argin.lerit ,
This is the error now I am getting.
Can you paste here again what your query is, you might have missed a parentheses when copying?
Thanks!
"insert into PphShopFindingReports (Id, RequestReceivedOn, EngineModel, PrimaryModuleSerialNumber, PrimaryModule2SerialNumber, SecondaryModuleSerialNumber, ReasonForRemovalBW, PrimaryModuleWorkScopeFinal, PrimaryModule2WorkScopeFinal, SecondaryModuleWorkScopeFinal, PrimaryModuleTSN, PrimaryModuleCSN, PrimaryModuleTSO, PrimaryModuleCSO, PrimaryModuleTSHSI, PrimaryModuleTSLV, PrimaryModuleCSLV, PrimaryModule2TSN, PrimaryModule2CSN, PrimaryModule2TSO, PrimaryModule2CSO, PrimaryModule2TSHSI, PrimaryModule2TSLSV, SecondaryModuleTSN, SecondaryModuleCSN, SecondaryModuleTSO, SecondaryModuleTSLR, SecondaryModuleTSLV, HSICategory, PrimaryModuleOVHCategory, PrimaryModule2OVHCategory, SecondaryModuleOVHCategory, ProdSapUpdate, StagSapUpdate, SDOrder, ReasonForRemovalAdditionalInfo, SBIncorpDuringShopVisit, DateOfRemoval, FileName, Status, ReplaceWorkScope, StagUpdate, ProdUpdate, [File], StatusId, Description, ProcessTime, RequestUserId, IsLocked, LockedTime, ShopEventDateRecieved, Discrepancy, EngineMarket, ) values (' ',' ','"+APS1+"',' ',' ',' ','"+Reason_remove+"','"+power_sec+"','"+Pri_load+"','"+Sec_gear+"','"+tt1+"','"+tslv1+"',' ',' ',' ','"+tsr2+"','"+cslv1+"',' ', ' ', ' ', ' ', ' ', ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','"+ SD1+"','"+add_info_rm+"','"+SB_incorp1+"','"+str2+"','"+filename+"',' ',' ',' ',' ', ' ',' ', ' ', ' ', ' ', ' ', ' ', '"+str1+"', ' ', ' ')"
Please look into it.
@argin.lerit ,
This error now I am getting.
Please check exactly how many columns there are and compare them with the values. It needs to be a perfect match. There are only 53 columns but 54 values.
Thanks!
This error I got now.
Looks like an issue with the data types in your table columns. Make sure they are all VARCHARs. The message implies at least one of them is VARBINARY. If you do not have control over the data types of the table columns, find out which ones those are and then use CONVERT on the specific values: sql server - Converting a VARCHAR to VARBINARY - Database Administrators Stack Exchange
ashokkarale
Yoichi
singh_sumit
sven.wullum1
adi.mehare
ppr
sonaliaggarwal47
shahidh.aqeel.shahul
Akash_Javalekar1
Sami_Rajput