Import data truncate my data

I am using SQL 2005. I have a table defined as decimal(12,4).
I import data like 1.99305 to it, and the value in the database becomes
1.9930 instead of 1.9931.
But, when I edit the the database like
update myTable set price = 1.99305 where id = '12345'
the value of the Price becomes 1.9931.
How can I make the import program import the data to be 1.9931 instead of
1.9930 ?
Thank you.
CREATE TABLE myTable (
id int
Price decimal(12,4) NULL
)
1.99305It will be kind of hard to aanwer without knowing the importing tool.
Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am using SQL 2005. I have a table defined as decimal(12,4).
>I import data like 1.99305 to it, and the value in the database becomes
>1.9930 instead of 1.9931.
>But, when I edit the the database like
>update myTable set price = 1.99305 where id = '12345'
>the value of the Price becomes 1.9931.
>How can I make the import program import the data to be 1.9931 instead of
>1.9930 ?
>Thank you.
>CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
>1.99305
>|||I was using the Copy wizard in the Microsoft SQL Server Management
Studio (right click on the database - Tasks - Copy Database)
On Jun 22, 4:24 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> It will be kind of hard to aanwer without knowing the importing tool.
> Is it using Bulk Insert? BCP? DTS? SSIS? Something else?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 22 Jun 2007 15:36:05 -0500, "fniles" <fni...@.pfmail.com>
> wrote:
>
>
>
>
>
> - Show quoted text -|||(fiefie.niles@.gmail.com) writes:
> I was using the Copy wizard in the Microsoft SQL Server Management
> Studio (right click on the database - Tasks - Copy Database)
This I don't get:
[vbcol=seagreen]
When you use the Copy Database Wizard, you are copying the entire database
definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
and this you cannot get this truncation issue. Or am I missing something?
Another story is that CDW offers to method of copying: Attach/Detach
and the SMO method, and the latter method is very unreliable, and nothing
you can use seriously. I think that by SP2, it has reached the state
where it can be called a beta, but it is far from being trustworthy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
Sorry, I was not clear. I was copying from an Access database to the SQL
Server database, and the datatype in the Access is Text(50).
The data in the Access database is 1.99305, and when it copied to SQL Server
it became 1.9930 instead of 1.9931.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99589C25392E0Yazorman@.127.0.0.1...
> (fiefie.niles@.gmail.com) writes:
> This I don't get:
>
> When you use the Copy Database Wizard, you are copying the entire database
> definition, so you cannot copy from, say, decimal(14,6) to decimal(12,4),
> and this you cannot get this truncation issue. Or am I missing something?
> Another story is that CDW offers to method of copying: Attach/Detach
> and the SMO method, and the latter method is very unreliable, and nothing
> you can use seriously. I think that by SP2, it has reached the state
> where it can be called a beta, but it is far from being trustworthy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:

[vbcol=seagreen]
>Sorry, I was not clear. I was copying from an Access database to the SQL
>Server database, and the datatype in the Access is Text(50).
>The data in the Access database is 1.99305, and when it copied to SQL Serve
r
>it became 1.9930 instead of 1.9931.
I am still not clear, since the tool you say you used does not work
with Access.
When I open Microsoft SQL Server Management Studio, right click on a
database, and choose the Tasks / Copy Database... option, the Copy
Database Wizard opens. The message in that window is "You can use
this wizard to move or copy databases from an instance of SQL Server
2000 or SQL Server 2005 to an instance of SQL Server 2005."
Roy Harvey
Beacon Falls, CT|||I am very sorry, I meant to say I was using the Microsoft SQL Server
Management Studio Import wizard, right click on a
database, and choose the Tasks / Import Data... option to import from Access
database to SQL Server database (I chose Access as the source database and
SQL Server as the destination database)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31kv73dpnmkfh7srad18og9de1alac8c4t@.
4ax.com...
> On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
> wrote:
>
>
> I am still not clear, since the tool you say you used does not work
> with Access.
> When I open Microsoft SQL Server Management Studio, right click on a
> database, and choose the Tasks / Copy Database... option, the Copy
> Database Wizard opens. The message in that window is "You can use
> this wizard to move or copy databases from an instance of SQL Server
> 2000 or SQL Server 2005 to an instance of SQL Server 2005."
> Roy Harvey
> Beacon Falls, CT|||I am not familiar with the import tool in 2005, but I just played with
it a bit. I assume that the wizard did not assign decimal(12,4) as the
data type when Access has Text(50), so I assume the table definition
was done before the import.
It would appear that you have found a shortcoming of the import
process when crossing data types. The only workaround I can see would
be to import to a table that matches the original datatypes, and then
move the data to the tables with the new types using SQL INSERERT
commands. That would allow you to use explicit CONVERT and ROUND
commands.
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 12:54:00 -0500, "fniles" <fniles@.pfmail.com>
wrote:

>I am very sorry, I meant to say I was using the Microsoft SQL Server
>Management Studio Import wizard, right click on a
>database, and choose the Tasks / Import Data... option to import from Acces
s
>database to SQL Server database (I chose Access as the source database and
>SQL Server as the destination database)
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31kv73dpnmkfh7srad18og9de1alac8c4t@.
4ax.com...
>|||On Jun 23, 2:36 am, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL 2005. I have a table defined as decimal(12,4).
> I import data like 1.99305 to it, and the value in the database becomes
> 1.9930 instead of 1.9931.
> But, when I edit the the database like
> update myTable set price = 1.99305 where id = '12345'
> the value of the Price becomes 1.9931.
> How can I make the import program import the data to be 1.9931 instead of
> 1.9930 ?
> Thank you.
> CREATE TABLE myTable (
> id int
> Price decimal(12,4) NULL
> )
> 1.99305
Hi, I was wondering, instead of using decimal(12,4) why not use
decimal(20,5) or decimal(20,10)? Then you can round your data once it
is imported in the server and alter the column to decimal(20,4) later?