insert sql syntax

Hi:
  I get the following error when I try something like:
insert into dbX.dbo.A select * from  dbX.dbo.B

String or binary data would be truncated.
The statement has been terminated.

The schema of both the tables are the same and look like:
a datetime,
b varchar(30),
c char(5),
d varchar(4),
e int,
f int,
g int,
h int,
i int,
j money,
k money,
l money,
m money)

Does anyone know why this error should come when the schemas are the same (so no fear of chop)? how can i work around this if not?

thanks
LuckyLucksAsked:
Who is Participating?
 
rafranciscoCommented:
>> I was wondering if there was a method tosplit a field on a character say lname, fname could be split into lname and fname based on the comma? <<

DECLARE @LName VARCHAR(100)
DECLARE @FName VARCHAR(100)
DECLARE @FullName VARCHAR(200)

SET @FullName = 'Lucks,Lucky'
SET @LName = LEFT(@FullName, CHARINDEX(',', @FullName) - 1)
SET @FName = RIGHT(@FullName, CHARINDEX(',', REVERSE(@FullName)) - 1)
0
 
rafranciscoCommented:
The schema of both tables may be the same, but make sure that the sequence of the columns in both tables are in the same order.  If the sequence of the columns are not the same, then you have to provide the column names in the INSERT statement as well as in the SELECT statement to make sure they match.
0
 
adwisemanCommented:
If this where me, I'd double check that both tables are the same.  Are these both SQL server tables?  Are these all the fields and datatypes in the tables?  Obviously you've used an example, any text, nvarchar, binary, Primary Keys, etc?

Then if I'm still getting the error, I'd explicitly write out the columns in the insert, then I can start taking them away untill the error goes away.  You may find the column that is causing the error.

insert into dbX.dbo.A(a, b, c, ...) select (a, b, c, ...) from  dbX.dbo.B


If the tables are identical, then you should not have an issue.  There's something that's not the same.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TorrwinCommented:
If that still doesn't work you could just export the data from table B to table A in enterprise manager.
0
 
LuckyLucksAuthor Commented:
the sequence is the same and so are the field names. The field that seems to be giving the prolem is of type varchar(4). It writes into the initial table B as lastname,firstname s.t the longest field has 20 chars. But when it tries the insert into A , it cant copy over the same field even tho they both are 4 varchars. How can I resolve this?

thanks
0
 
rafranciscoCommented:
In your INSERT ... SELECT statement, specify the column names.  Then in the column that has only 4 characters, use LEFT(ColumnName, 4) so that it will only insert 4 characters.
0
 
LuckyLucksAuthor Commented:
still doesnt. I was wondering if there was a method tosplit a field on a character say lname, fname could be split into lname and fname based on the comma?
0
 
LuckyLucksAuthor Commented:
     
ALso to note , I created the lname, fname monster from below in an sql stmt:
ltrim(rtrim(max(x.dbm_last_name)))+', '+ltrim(rtrim(max(x.dbm_first_name))) as "FullName"

and now when I do   ltrim(rtrim(FullName)) I get the entire lname, fname.
0
 
rafranciscoCommented:
>> and now when I do   ltrim(rtrim(FullName)) I get the entire lname, fname. << 

What do you mean by this?

To help you better, why not post the table definition of both your tables as well as the SQL statement you are trying to execute.
0
 
LuckyLucksAuthor Commented:
coming soon....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.