String or binary data would be truncated

dbtut

6 years ago

When we want to add data to a table, we get this error if the size of the column in the table is smaller than the data we want to add.

For example, with the help of the following script, let’s create a table named databaseandlogins in the TestDB database.

USE [TestDB]
GO
CREATE TABLE [dbo].[databaseandlogins](
[database_name] [varchar](10) NULL,
[login_name] [varchar](10) NULL,
) ON [PRIMARY]

USE [TestDB]

CREATE TABLE [dbo].[databaseandlogins](

[database_name] [varchar](10) NULL,

[login_name] [varchar](10) NULL,

) ON [PRIMARY]

Then try to add data to this table as follows.

USE [TestDB]
GO
INSERT INTO [dbo].[databaseandlogins]
           ([database_name]
           ,[login_name])
     VALUES
           ('database name more than ten characters','myLogin')

USE [TestDB]

INSERT INTO [dbo].[databaseandlogins]

([database_name]

,[login_name])

VALUES

('database name more than ten characters','myLogin')

When we try to add data in this way, we will get an error as follows.

To overcome this problem, we must increase the size of the column named database_name, which we have defined as varchar(10). To increase the size of a column in SQL Server , you can read my article “ALTER TABLE Statement in SQL Server(TSQL)”