Insert varbinary data into SQL Server database
I have this table:
(<NUM_TRF int ,<NAME, varchar(255),> ,<DESCRIPTION, text,> ,<REPORT, varbinary(max),>)
I try to create a script in SQL Server 2008, in order to insert a line on my local database,
INSERT INTO [MY_DB_APP].[dbo].[CONNECT_USER] VALUES(1, 'name', 'description', Cast('wahid' As varbinary(max)) ) GO
but I get this error:
String or binary data would be truncated.
The statement has been terminated.
2 Solutions collect form web for “Insert varbinary data into SQL Server database”
Issue #1: don’t use
TEXT anymore – it’s deprecated. Use a
VARCHAR(n) with a suitable size of
n, or if you really must (only if you REALLY must), use
CREATE TABLE dbo.CONNECT_USER ( NUM_TRF int, NAME varchar(255), DESCRIPTION varchar(1000), REPORT varbinary(max) )
I would personally also avoid writing EVERYTHING IN ALL CAPS – this just makes it so much harder to read! And I would try to avoid very generic column names like
Description – those are not very intuitive, and might collide with other table’s columns and / or with SQL Server reserved keywords. Try to use more expressive, more context-related column names that make sense in your environment (
ConnectUsername or whatever)
Issue #2: when doing an
INSERT, I would recommend to always define the column you want to insert into. This avoids unpleasant surprises when a table is restructured or new columns are added:
INSERT INTO [MY_DB_APP].[dbo].[CONNECT_USER](NUM_TRF, NAME, DESCRIPTION, REPORT) VALUES(1, 'name', 'description', CAST('wahid' AS VARBINARY(MAX))) GO
Hmm, “trivial answer converted to comment”. But it’s the right answer, and as a comment, there’s no way to accept it as the right answer. And as a user with less than 50 reputation, I don’t technically have permission to make comments.
So, I’ll say again, non-trivially this time…
This question is answered here: Update table inserting VARBINARY data