CODEWITHSUNDEEP
sqlsql-server-2008ssms
Jonathan
Jonathan

Reputation: 5

Can't update table in SQL Server

I am using SQL Server and SSMS 2008. I am in the correct database.

Sorry if this is obvious. I have the following table with no PK to start off with so I can easily make changes until the process works for me

CREATE TABLE Staging.Country_code 
(
    Country varchar(200) NOT NULL,
    ISO_2 varchar(2) DEFAULT 'Not Available',
    ISO_3 varchar(3) DEFAULT 'Not Available',
    ISO_numeric int
)
GO

I successfully inserted the following:

INSERT INTO Staging.Country_code (Country, ISO_2, ISO_3, ISO_Numeric)
   SELECT Country, ISO_2, ISO_3, ISO_Numeric
   FROM RawStaging.Country_code
   WHERE Country IS NOT NULL
GO

Now I want to insert other items into it:

First I tried:

INSERT INTO Staging.Country_code (Country)
   SELECT DISTINCT 
      Staging.allYears.Country 
   FROM 
      Staging.allYears
   WHERE 
      Staging.allYears.Country NOT IN (SELECT DISTINCT Staging.Country_code.Country 
                                       FROM Staging.Country_code)
GO

And got error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated. The statement has been terminated.

Then I tried simply (among other things including trying to cast to varchar(200) a single text item to insert it into the Country column):

INSERT INTO Staging.Country_code (Country, ISO_2, ISO_3, ISO_numeric)
VALUES ('Africa', 'Test', 'Test', 0);

And still get same error.

I checked the data types on the table and they are still exactly as in the create statement.

I am only an SQL beginner, I just cant see why this might not work. There is definitely data in the table I have selected the first import and it is still there. Nothing is referencing the table.

Am I missing something really obvious?

Upvotes: 0

Views: 1560

Answers (1)

Joe Enos
Joe Enos

Reputation: 40393

Your columns:

ISO_2 varchar(2) DEFAULT 'Not Available'
ISO_3 varchar(3) DEFAULT 'Not Available'

Your column lengths are only 2 and 3, and you're attempting to insert a record in the table without putting anything in those columns, which means it will use the default values. The default value of 'Not Available' is larger than 2 and 3 characters long, so it won't fit in those columns. Same goes for 'Test'.

Whenever you get the "String or binary data would be truncated" message, it means you're trying to squeeze something into a column where the data is too big. Unfortunately, SQL Server doesn't seem to want to help by telling you which column.

Upvotes: 6

Related Questions