-2

I am doing school project and I have to create table and insert data by myself. The following is my SQL statements. 

CREATE TABLE Package
(pkgcode char(4) primary key,
 pkgname varchar(30) not null,
 pkgdescription varchar(60) not null,
 pickuplocation varchar(20) not null,
 costperadult decimal (7,2) not null,
 costperchild decimal (7,2) not null,
 pkgremarks varchar(30) null)

INSERT INTO Package
VALUES
('K009', 
 '8D7N FUJIAN/MT.WUYI', 
 'A whole new experience: enjoy multi nation architecture in Gulangyu Island and UNESCO Mt.Wuyi', 
 'Jurong East', 
 700.00, 
 500.00, 
'Hotel accommodation will be 4 stars.')

But I got the following error which is at the insert statement.

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

I would appreciate if anyone of you can help me. Thank you.

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
Grace
  • 13
  • 1
  • 7
  • 4
    Google....You seriously need to google more – Hozikimaru Jul 25 '18 at 16:31
  • 1
    it usually means the text is longer than the field it is trying to go into. For long pieces of text, consider the varchar(max) or nvarchar(max) field type – Cato Jul 25 '18 at 16:31
  • Is 'A Whole ...' string <= 60 characters? BTW do not replace with varchar(MAX) but a more acceptable value for your needs. – Cetin Basoz Jul 25 '18 at 16:31
  • 3
    Possible duplicate of [error, string or binary data would be truncated when trying to insert](https://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert) – Aaron Dietz Jul 25 '18 at 16:31
  • 2
    Your description is longer than the 60 characters you allowed for the `pkgdescription` column. Count the letters. If there's not room for them, they would have to be truncated (cut off). You'll have the same issue on `pkgremarks` as well. Read the message and look at the code you've written. You can't force more into the column than you've allocated space to hold. – Ken White Jul 25 '18 at 16:33
  • 3
    Your `pkgdescription` column can hold 60 characters, but the description you are trying to insert is 93 characters. – Aaron Dietz Jul 25 '18 at 16:33

1 Answers1

3

You're trying to insert a description that's too long for the column's specification. Just create the column with a larget size (e.g., here 100 characters should suffice) and you should be OK:

CREATE TABLE Package
(pkgcode char(4) primary key,
 pkgname varchar(30) not null,
 pkgdescription varchar(100) not null, -- Here!
 pickuplocation varchar(20) not null,
 costperadult decimal (7,2) not null,
 costperchild decimal (7,2) not null,
 pkgremarks varchar(30) null
)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • OP should figure out the max length they really need to support; based on the column name and their cited issue is at 93, I think 100 wouldn't be enough either. – UnhandledExcepSean Jul 25 '18 at 16:40
  • Once you are using varchar it really does not matter how wide you make it (up to some very large size in the GB). SQL Server uses as much space as it needs to save the data. So why don't you use 500? – benjamin moskovits Jul 25 '18 at 16:49