Solved

SQL 2008 INSERT STATEMENTS

Posted on 2010-09-16
9
418 Views
Last Modified: 2012-05-10
I am having an error inserting the following data in SQL

USE ITD640_GP4

INSERT INTO customerTbl (customerID,customerFname,customerLname,customerTel,customerCity,customerState,customerZip)
Values
      ('114','Alan','Smith','3235','Mobile','Alabama','36509'),
      ('115','Kevin','Gordon','1124','Gulf Shores','Alabama','36535'),
      ('116','Paul','Connell','1212','Gulf Shores','Alabama','36535'),
      ('117','James','Sampson','1235','Alexandria','Louisiana','80977'),
      ('118','George','Ruiz','7532','Alexandria','Louisiana','80977'),
      ('119','Peter','Johnson','3254','Alexandria','Louisiana','80977'),
      ('120','Ben','Simpson','3241','Alexandria','Louisiana','80977'),
      ('121','Ken','Gordon','6643','New York','New York','70011')
      
      
SELECT * FROM customerTbl

The error that I am gettiong is

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

(2 row(s) affected)

Any clues on what i can do to fix this matter.?
0
Comment
Question by:TAMSCODAN
9 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33697509
how long are your character fields declared? you seem to try so store a value longer then the field max size
0
 
LVL 4

Expert Comment

by:timexist
ID: 33697586
String or binary data would be truncated.
The statement has been terminated.

It means the value want to insert is larger than the max limitation of the field.
so you need to check the fields of customerTbl .

0
 
LVL 3

Author Comment

by:TAMSCODAN
ID: 33697600
HOw do i disable the option preventing to save changes to my table. I was trying to change the size
0
 
LVL 4

Expert Comment

by:timexist
ID: 33697606
you have to close the openning table, then you can save it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:xiong8086
ID: 33697885
I think your data is larger than the allowed size set for your column by definition.

if you want to turn off the exception
you may add:

set ANSI_WARNINGS OFF
GO
....

after scripts finished
....
set ANSI_WARNINGS ON
GO

you may execute without warning, but you may lose some data as it will be truncated if beyond the size limit.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33699076
Increase the datatype length of the column which is causing this error. Then only the data you are trying to insert into the table will get inserted successfully. If you suppress the error warning messages, you will lose those data that you are inserting.

So better to first check and find which column have less datatype length than the size of the data you are inserting. Then increase its datatype length. That's all.

Raj
0
 
LVL 3

Accepted Solution

by:
jvejskrab earned 500 total points
ID: 33701026

tools -> options -> designers -> table and database designers -> prevent saving changes.......
0
 
LVL 4

Expert Comment

by:parthmalhan
ID: 33710979
Please provide Table Definition/structure.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34119902
Dear TAMSCODAN,

Any update ?

Raj
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now