data copy questions ...

I want to copy data from one sql server table to another sql server table.
In the original table almost all columns are of data type = 'varchar' , in
the destination table, I would like to convert columns into various data
types like 'nvarchar', 'numeric', date, etc.
When I try to issue the insert statement, it wouldn't allow me since values
in some columns is NULL and I get the error - "error converting null into
<...> format"
I tried to issue an insert for one column like -
insert dest_table (OrderNo) (select orderno from orig_table where orderno IS
NOT NULL)
I am able to insert values using this statement where the column is not null
.
I have over 50 such columns and with different data types. I am wondering if
there is any way by which I can issue a statement like
insert dest_table (select * from orig_table)
without having to check for NULL values for individual columns and
also without having to issue CONVERT(decimal, salesamt). It should pick up
the data type from the destination table.
If this is not the right group, please let me know so that I can post it on
the right one.
Thank you,
-MeMe -
Two ways:
1 - Redefine your target table columns to allow NULLs. Then you can do
Insert Into Select * from... if everything is defined to line up properly.
2 - In your select for each column that allows NULL use a COALESCE to
convert NULLs into some default value.
RLF
"Me" <Me@.discussions.microsoft.com> wrote in message
news:7FC22119-153E-44D9-967B-9BA57E584387@.microsoft.com...
>I want to copy data from one sql server table to another sql server table.
> In the original table almost all columns are of data type = 'varchar' , in
> the destination table, I would like to convert columns into various data
> types like 'nvarchar', 'numeric', date, etc.
> When I try to issue the insert statement, it wouldn't allow me since
> values
> in some columns is NULL and I get the error - "error converting null into
> <...> format"
> I tried to issue an insert for one column like -
> insert dest_table (OrderNo) (select orderno from orig_table where orderno
> IS
> NOT NULL)
> I am able to insert values using this statement where the column is not
> null.
> I have over 50 such columns and with different data types. I am wondering
> if
> there is any way by which I can issue a statement like
> insert dest_table (select * from orig_table)
> without having to check for NULL values for individual columns and
> also without having to issue CONVERT(decimal, salesamt). It should pick up
> the data type from the destination table.
> If this is not the right group, please let me know so that I can post it
> on
> the right one.
> Thank you,
> -Me
>|||Russell,
Thanks for your timely response. Your suggestion helped me trace out the
root of the problem.
It was due to bad data that I was unable to copy records over. For eg. in
one case, the date field had value of 189 so it couldn't convert into date.
I
fail to understand for another field the actual text in the field was more
than the allowed field length. It should have at least dispalyed the field
for which it was over the maximum length.
Anyways, I resolved it now and thanks for your and everyone else in
newsgroups for their help at all times.
-Me
"Russell Fields" wrote:

> Me -
> Two ways:
> 1 - Redefine your target table columns to allow NULLs. Then you can do
> Insert Into Select * from... if everything is defined to line up properly.
> 2 - In your select for each column that allows NULL use a COALESCE to
> convert NULLs into some default value.
> RLF
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:7FC22119-153E-44D9-967B-9BA57E584387@.microsoft.com...
>
>