Solved

Msg 8152, Level 16, State 14, Line 1

Posted on 2010-09-23
29
1,546 Views
Last Modified: 2012-08-13
I am using the insert to command to insert data into my table and it gives me this error message for line 1.
Line 1 is the insert to line, why am I getting this for the first line.
COde is below

INSERT INTO employees
      (      LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneAreaCode,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age)
      
      VALUES
      ('Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'),
      ('Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'),
      ('Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'),
      ('Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24');
      

Thanks in advance

IKE
0
Comment
Question by:mrwelshfma
  • 13
  • 10
  • 4
  • +2
29 Comments
 
LVL 15

Expert Comment

by:crisco96
ID: 33746877
I think you have to do an insert for each one (code below)
INSERT INTO employees

      (      LastName,

            FirstName,

            address,

            city,

            state,

            TelephoneAreaCode,

            TelephoneNumber,

            HireDate,

            Salary,

            Gender,

            Race,

            Age)

      

      VALUES

      ('Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24')



INSERT INTO employees

      (      LastName,

            FirstName,

            address,

            city,

            state,

            TelephoneAreaCode,

            TelephoneNumber,

            HireDate,

            Salary,

            Gender,

            Race,

            Age)

      

      VALUES

      ('Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18')



INSERT INTO employees

      (      LastName,

            FirstName,

            address,

            city,

            state,

            TelephoneAreaCode,

            TelephoneNumber,

            HireDate,

            Salary,

            Gender,

            Race,

            Age)

      

      VALUES

      ('Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25')



INSERT INTO employees

      (      LastName,

            FirstName,

            address,

            city,

            state,

            TelephoneAreaCode,

            TelephoneNumber,

            HireDate,

            Salary,

            Gender,

            Race,

            Age)

      

      VALUES

      ('Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24')

      

Open in new window

0
 

Author Comment

by:mrwelshfma
ID: 33746903
I don't think so,
I did this exact same coding last evening but with different peoples info and it worked fine with no errors.
The only thing I changed today is the data values (names, addresses, phone,...) for each person.

Thanks IKE
0
 

Author Comment

by:mrwelshfma
ID: 33746929
Here is the coding I did yesterday evening and it worked without errors.

INSERT INTO employees
      (      LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneAreaCode,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age)
      
VALUES
      ('Edelman', 'Glenn', '175 Bishops Lane', 'La Jolla', 'CA','619', '5550199', '10/7/2003', '21500', 'm', 'Caucasian', '64'),
      ('McMullen', 'Eric', '763 Church Street', 'Lemon Grove', 'CA', '619', '5550133','11/1/2002', '13500', 'm', 'Caucasian', '20'),
      ('Slentz', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', '619', '5550123', '6/1/2000', '48000', 'm', 'Asian', '34');


IKE
0
 
LVL 8

Accepted Solution

by:
Mohit Vijay earned 500 total points
ID: 33746950
USe it as

INSERT INTO employees
      (      LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneAreaCode,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age)
     
      SELECT 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'
UNION All

SELECT 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'

UNION ALL
      SELECT 'Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'

UNION ALL
      SELECT 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24'
0
 

Author Comment

by:mrwelshfma
ID: 33746972
OK VjSoift,

I am trying to add these employees data to my employees table in my database,

Why would I need to use the select command? (isn't that for data that is arleady in the table)

Thanks in advance.

IKE
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747020
AS mrwelshfma told, you cannot insert multiple value as you want. ITS REALLY TRUE. because you dont want to split your insert statement, I just provide you a work arround you on it. try it and enjoy it :)
0
 

Author Comment

by:mrwelshfma
ID: 33747057
OK I will try your example and let you know if it works.

IKE
0
 

Author Comment

by:mrwelshfma
ID: 33747087
Hi VjSoft,

I tried what you gave me and it gave me the following error message.

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

So now what can I try?
0
 

Author Comment

by:mrwelshfma
ID: 33747099
What I don't understand is why it is trying to say that the data value for line 1 is too long for the line??
there is no length limit set on this line

IKE
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747121
below statements are fine

-----------------------
 SELECT 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'
UNION All

SELECT 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'

UNION ALL
      SELECT 'Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'

UNION ALL
      SELECT 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24'
-----------------------------

You can try and execute above statement.

Now about error.

Please check field length of
 LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneAreaCode,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age

these should be fare enought to hold data that you are passing. If you dont want to try it by count lenght, make all as varchar(255).
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747129
privde me the create table script of your table, I will give the exact answer.
0
 

Author Comment

by:mrwelshfma
ID: 33747154
OK How can I change the values for the varchar for each column in table?

Do I need to edit the talble properties or how do I do it?

I am just learning this for a class and still am totally confused by it all

Thanks in advance.

IKE
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747171
open SQL management consol, edit table in design mode and you can change it there.

I am not providing you alter table script because you are new to SQL and it will be difficult for you to understand. best way is to change it by modifying design manually.
0
 

Author Comment

by:mrwelshfma
ID: 33747180
Here is the script for the create tables entry.

create table employees
(Employee_ID numeric Identity,
LastName Varchar(30),
FirstName varchar(30),
address varchar(50),
city varchar(25),
state varchar(20),
TelephoneArea_Code varchar(3),
TelephoneNumber varchar(7),
HireDate varchar(12),
Salary varchar(7),
Gender varchar(8),
Race varchar(13),
Age integer,
Job_Title_ID numeric);
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:mrwelshfma
ID: 33747192
Thank you so much for your help Vj,

I will have to wait until later this eveing to try the step you gave me above,

Thanks so much for your help.

IKE
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747210
do you have any data in your table?
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33747265
if you dont have any data in your table, then use it

 DROP TABLE employees
 create table employees
(Employee_ID numeric Identity,
LastName Varchar(50),
FirstName varchar(50),
address varchar(255),
city varchar(50),
state varchar(50),
TelephoneArea_Code varchar(4),
TelephoneNumber varchar(10),
HireDate varchar(16),
Salary varchar(12),
Gender varchar(8),
Race varchar(20),
Age integer,
Job_Title_ID numeric)
 
 INSERT INTO employees
      (      LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneArea_Code,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age)
     
      SELECT 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'
UNION All

SELECT 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'

UNION ALL
      SELECT 'Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'

UNION ALL
      SELECT 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24'

 SELECT 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'
UNION All

SELECT 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'

UNION ALL
      SELECT 'Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'

UNION ALL
      SELECT 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33749999
>>AS mrwelshfma told, you cannot insert multiple value as you want. ITS REALLY TRUE.<<
Actually, no.  it is perfectly legal, provided they are using SQL Server 2008 with a database in compatibility level set to 10 or greater.

They (abbreviated) syntax for INSERT is :
INSERT
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ]
    { <object> | rowset_function_limited
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ]
    [ <OUTPUT Clause> ]
    { VALUES ( ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] )
    | derived_table
    | execute_statement
    | <dml_table_source>
    | DEFAULT VALUES
    }
}
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33750015
Here is the same one from SQL Server 2005:

INSERT
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO]
    { <object> | rowset_function_limited
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ]
    [ <OUTPUT Clause> ]
    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
    | derived_table
    | execute_statement
    }
}

Do you see the difference?
0
 

Author Comment

by:mrwelshfma
ID: 33750168
Hi Vjsoft

I tried the coding you gave me with the Drop command and it give me the following error.

Msg 207, Level 16, State 1, Line 18
Invalid column name 'TelephoneAreaCode'.

What is causing this issue now??
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33750353
I would have to guess a typo.  In the code posted earlier they use "TelephoneArea_Code"
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33750552
either you use "TelephoneAreaCode" as column name or "TelephoneArea_Code", whatever you decide, relace other occurance everwhere.
0
 

Author Comment

by:mrwelshfma
ID: 33750639
Thanks guys I got it figured out, it was actually a TYPO in the table/ columns listing the column was listed with the wrong spelling and it caused the error!

Whew how demanding SQL is
But I geuss once you learn it and practice it enough it is easy! :)
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33750661
After correcting type error, are you able to insert multiple values with your one insert statement (as you were doing initially)?
0
 

Author Closing Comment

by:mrwelshfma
ID: 33750687
After some debate back and forth and a few trial and error steps, it finally worked and I got my work done.

IKE
0
 

Author Comment

by:mrwelshfma
ID: 33750694
@ ViSoft,
I was able to get all my lines inserted into the table and it didn't give any errors!!

Thank you so much for the assistance.

IKE
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33750702
Is the below sentace worked for you as it is? If yes, which version of MS-sQL Server you are using?

---------------------------------------------------------
INSERT INTO employees
      (      LastName,
            FirstName,
            address,
            city,
            state,
            TelephoneAreaCode,
            TelephoneNumber,
            HireDate,
            Salary,
            Gender,
            Race,
            Age)
     
      VALUES
      ('Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA','760', '5550100', '3/12/2003', '10530', 'f', 'Caucasian', '24'),
      ('Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '619', '5550154','11/1/2003', '15000', 'm', 'African American', '18'),
      ('Esquivez', 'David', '10983 N Coast Hwy Apt 902', 'Encinitas', 'CA', '760', '5550108', '7/25/2003', '18500', 'm', 'Hispanic', '25'),
      ('Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', '858', '5550135', '7/12/2003', '21000', 'f', 'caucasian', '24');
-----------------------------------------------------------
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 33751617
Note: The above would work only on MSSQL 2008 and above. With previous versions you need to use the
    insert ... select ... union select ..
approach.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33753141
Also, and as I stated previously the database has to be set to a compatibility level of 10 or higher in order to accept the new syntax.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

21 Experts available now in Live!

Get 1:1 Help Now