LuckyLucks
asked on
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
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
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If that still doesn't work you could just export the data from table B to table A in enterprise manager.
ASKER
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
thanks
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ALso to note , I created the lname, fname monster from below in an sql stmt:
ltrim(rtrim(max(x.dbm_last
and now when I do ltrim(rtrim(FullName)) I get the entire lname, fname.
>> 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.
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.
ASKER
coming soon....