Problems Inserting Data- Am I doing something wrong?

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)

Insert

into customer(CustomerID, LastName, FirstName, BillingAddress, City,State, Country, Zipcode, PhoneNumber, EmailAddress)

select

CustomerID,(left(ContactName,charindex(' ', ContactName)-1))as LastName,

(right(

ContactName,charindex(' ',reverse(ContactName))-1))as FirstName,

Address

, City,Region,PostalCode,Country,Phonefrom Northwind.dbo.Customers

I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my application

Any help would be of great

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

|||

Hi Stew,

I corrected it in the actual query (when I executed) and it gives me this error message....any pointers??

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

Thank you

|||Look at the size constraints of the columns you are inserting too and see if they make sense against what is being inserted. You may have a string field going into a char field, or something of the sort.|||

Stew312:

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

Looks like the problem is this:

This is what my query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber

There is only one FROM COLUMN (ie., Contactname) but is repeated twice...I mean to say in

the Northwind customer database both the last name and first name are put in a single column

and I am trying to separate it into two colums (Lastname , Firstname).....Is it a wrong way of doing things.....I had been breaking my on this for couple of hours

your help is greatly appreciated

|||Too embrassing and a costly mistake (had to waste most of my time).........the order of columns I was trying to insert was wrong....though embrassing, I am sharing because some people (including me) tend to forget minor details while focusing on other major problems.....so moral of story....Pay attention to detail and don't forget basics.