The reason is you are trying to insert data which exceeds any of your existing column length. So in this scenario you have to increase the column size programatically. Another common problem is
Insert Error: Column name or number of supplied values does not match table definition. The problem is you are trying to insert n no of columns data into a table whereas table has n-1 columns. So in this scenario you need to add a column using T-Sql. Ok now i will describe how we can do this.
1. Add a new column to a table
2. Modify or Resize an existing column
3. Rename an existing column
4. Drop/Delete/Remove an existing column
5. Rename a Table
6. Drop/Delete/Remove an existing Table
Add a new column to a table:
The ALTER TABLE statement include an ADD clause, followed by the new column specification like datatype & length.
ALTER TABLE Sales ADD PartnerComission DECIMAL(18,2)
ALTER TABLE LineItem ADD SecStatus INT NOT NULL
Note: If you have a table with data then you can't add a not nullable column. The solution is first add a null column & then update this column data & then change allownull to false. If your table has no data then you can easily do what you want.
Modify or Resize an existing column:
Almost same as adding a new column except ADD will be changed to ALTER COLUMN.
Ok now we will change the allow null constraint of previous partnercomission column to false.
Note: To change allow null property of a column to False all rows must have contains the value
for this column otherwise you will get "ERROR: 'Sales' table- Unable to modify table. Cannot insert the value NULL into column 'TableName', table 'dbo.Sales'; column does not allow nulls. INSERT fails. The statement has been terminated."
Note: Keep in mind that if the table Articles contains length of any Notes column data greater than 1000 then you will get the ERROR: String or binary data would be truncated.
The another change is you may want to change the datataype of an existing column. Then must keep in mind that the new datatype must be cmpatible with existing datatype otherwise you may loose data or get the ERROR: Conversion failed when converting the Previous DataType value '... ' to data type New DataType.
Rename an existing column:
@objname = 'TableName.OldColumnName',
@newname = 'NewColumnName',
@objtype = 'COLUMN'
Drop/Delete/Remove an existing column:
By using the DROP COLUMN clause, one can remove an existing column. This permanently deletes the column and all of its contents or data. For example, the following command drops the Notes column from the Articles table.
Rename a Table:
The builtin "sp_rename" procedure can be used to change or rename the name of an existing table. In this case, the existing name and new name must be provided.
Drop/Delete/Remove an existing Table:
If you need to permanently delete or remove an existing table then you can use DROP TABLE statement which will permanently delete your table as well as data. Keep in mind that if the table under a relationship then you can not delete master table without deleting the chaild table first. The syntax for delete command is given below: