Forum Discussion

mansoorabid's avatar
mansoorabid
Copper Contributor
Jan 26, 2024

SQL Query Issue

I have two table 
Table1 and Table2 
 
 
CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[User_Id] [varchar](50) NULL,
[counts] [int] NULL,
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
CREATE TABLE [dbo].[table2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[User_Id] [varchar](100) NULL,
        [Date_Created] [datetime] NULL,
[Page_Called] [varchar](50) NULL,
[Method_Invoked] [varchar](50) NULL,
[Server_Name] [varchar](50) NULL,
[Comments] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
 
 
 
 
 
My below Query fails
 
INSERT INTO Table1 (User_id, counts)
 
SELECT User_Id, count(*) as counts
 
FROM Table2
 
WHERE User_Id is not null
 
and Date_Created >= '2024-01-26 09:14:02.880'
 
GROUP BY User_Id
 
I get the error “String or binary data would be truncated.”
 
But when I change to below Query with substring, it works
 
INSERT INTO Table1 (User_id, counts)
SELECT substring(User_Id,1,30), count(*) as counts
 
FROM Table2
 
WHERE User_Id is not null
 
and Date_Created >= '2024-01-26'
 
GROUP BY User_Id
 
What could be the issue
  • I get the error “String or binary data would be truncated.”

    As the error message clearly says and the cause is

    [dbo].[Table1] => [User_Id] [varchar](50)

    [dbo].[table2] => [User_Id] [varchar](100)

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor

    mansoorabid 

     

    I don't see any issue. Everything is behaving precisely as it should.

     

    From table1, [User_Id] is set to a maximum of 50 characters while in table2, [User_Id] is set to 100 characters. Any standards-compliant SQL engine should throw an error if you try and INSERT [User_Id] from table2 into table1.

     

    In your second example, you're truncating [User_Id] back down to a maximum of 30 characters, which fits into the limit of 50 from table 1, so there is no longer an error condition.

     

    So, as I say, everything is fine and behaving exactly as it should.

     

    Cheers,

    Lain

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    I get the error “String or binary data would be truncated.”

    As the error message clearly says and the cause is

    [dbo].[Table1] => [User_Id] [varchar](50)

    [dbo].[table2] => [User_Id] [varchar](100)

Resources