[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • Last Modified:

Msg 8152, Level 16, State 14, Line 1

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
mrwelshfma
Asked:
mrwelshfma
  • 13
  • 10
  • 4
  • +2
1 Solution
 
crisco96Commented:
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
 
mrwelshfmaAuthor Commented:
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
 
mrwelshfmaAuthor Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Mohit VijayCommented:
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
 
mrwelshfmaAuthor Commented:
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
 
Mohit VijayCommented:
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
 
mrwelshfmaAuthor Commented:
OK I will try your example and let you know if it works.

IKE
0
 
mrwelshfmaAuthor Commented:
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
 
mrwelshfmaAuthor Commented:
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
 
Mohit VijayCommented:
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
 
Mohit VijayCommented:
privde me the create table script of your table, I will give the exact answer.
0
 
mrwelshfmaAuthor Commented:
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
 
Mohit VijayCommented:
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
 
mrwelshfmaAuthor Commented:
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
 
mrwelshfmaAuthor Commented:
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
 
Mohit VijayCommented:
do you have any data in your table?
0
 
Mohit VijayCommented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
mrwelshfmaAuthor Commented:
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
 
Anthony PerkinsCommented:
I would have to guess a typo.  In the code posted earlier they use "TelephoneArea_Code"
0
 
Mohit VijayCommented:
either you use "TelephoneAreaCode" as column name or "TelephoneArea_Code", whatever you decide, relace other occurance everwhere.
0
 
mrwelshfmaAuthor Commented:
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
 
Mohit VijayCommented:
After correcting type error, are you able to insert multiple values with your one insert statement (as you were doing initially)?
0
 
mrwelshfmaAuthor Commented:
After some debate back and forth and a few trial and error steps, it finally worked and I got my work done.

IKE
0
 
mrwelshfmaAuthor Commented:
@ 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
 
Mohit VijayCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
Anthony PerkinsCommented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 13
  • 10
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now