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.99305
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" <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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/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/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/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 Server
>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 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.. .
>

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:
>
> >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- Hide quoted text -
> - 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:
>> >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.
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/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:
>> 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:
>> >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.
> 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/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Mon, 25 Jun 2007 07:44:31 -0500, "fniles" <fniles@.pfmail.com>
wrote:
>> I was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>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.
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 was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>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.
> 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 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 was using the Copy wizard in the Microsoft SQL Server Management
>> Studio (right click on the database - Tasks - Copy Database)
>>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.
>> 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
>|||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?

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?

Import + ActiveX (cross)

Hey,
1)
I'm doing a lot of importing (by DTS packages) from commaseparated files
into tables where i empty/truncate the table _before_ import of ALL info in
the file.
BUT - how do I import data from such a file by an UPDATE command...
Meaning if the table has an ID col and a NAME col, and the file has an ID
col and a NAME col - then I would like to be able to UPDATE all NAME-cols in
the table by using the info from the file. The NAME in the file might have
changed, but the ID stays the same... Also, i new ID's are in the file, they
should be INSERTed into the table (+ the NAME).
2)
I have been using ActiveX Data Trans for some imports - but it would be
great if I could perform SQL-taks (like above perhaps...? or is the
another way) - meaning: how do I make SQL call from within an ActiveX task?
Any help appreciated - Thanx!
Best regards
Jakob H. Heidelberg
Denmark> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
We do this every morning, or more accurately we have a scheduled task that
does this every morning. These are the general steps we use:
Select everything from the CSV file into a temporary table.
Update the rows that need updating in the target from the temp table, then
delete the source rows. You can do updates using joined tables, or create a
cursor and do them one record at a time.
Insert whatever's left in the temp table
Destroy the temp table.
Can't really help you with your other question; I could guess, but I'd
likely be wrong and sound more an idiot than normal. I leave that to the
rest of our colleagues.
William Morris
Product Development, Seritas LLC
Kansas City, Missouri|||Thanx.
can you tell me how to create the TEMP table, and how to INSERT into it?
Also, when will the table "die"?
Thanx in advance
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:c29sc1$1rlv$1@.news.cybercity.dk...
> Hey,
> 1)
> I'm doing a lot of importing (by DTS packages) from commaseparated files
> into tables where i empty/truncate the table _before_ import of ALL info
in
> the file.
> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
> 2)
> I have been using ActiveX Data Trans for some imports - but it would be
> great if I could perform SQL-taks (like above perhaps...? or is the
> another way) - meaning: how do I make SQL call from within an ActiveX
task?
>
> Any help appreciated - Thanx!
> Best regards
> Jakob H. Heidelberg
> Denmark
>
>
>

Import + ActiveX (cross)

Hey,
1)
I'm doing a lot of importing (by DTS packages) from commaseparated files
into tables where i empty/truncate the table _before_ import of ALL info in
the file.
BUT - how do I import data from such a file by an UPDATE command...
Meaning if the table has an ID col and a NAME col, and the file has an ID
col and a NAME col - then I would like to be able to UPDATE all NAME-cols in
the table by using the info from the file. The NAME in the file might have
changed, but the ID stays the same... Also, i new ID's are in the file, they
should be INSERTed into the table (+ the NAME).
2)
I have been using ActiveX Data Trans for some imports - but it would be
great if I could perform SQL-taks (like above perhaps...? or is the
another way) - meaning: how do I make SQL call from within an ActiveX task?
Any help appreciated - Thanx!
Best regards
Jakob H. Heidelberg
Denmark> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
We do this every morning, or more accurately we have a scheduled task that
does this every morning. These are the general steps we use:
Select everything from the CSV file into a temporary table.
Update the rows that need updating in the target from the temp table, then
delete the source rows. You can do updates using joined tables, or create a
cursor and do them one record at a time.
Insert whatever's left in the temp table
Destroy the temp table.
Can't really help you with your other question; I could guess, but I'd
likely be wrong and sound more an idiot than normal. I leave that to the
rest of our colleagues.
William Morris
Product Development, Seritas LLC
Kansas City, Missouri|||What I will do is create a temporary table in the same
database with the data from the file.
Next, I will write a script to (import) insert from the
temp table to where you want the data to go.
Mary
>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>|||You can do this without using Activex. As others
mentioned, you should import the text file into a temp or
staging table, then you have two choices:
1) delete from <main table> where id in (select id from
temptable);
insert into <main table> select * from temptable
2) update <main table> set name = b.name
from <main table> a inner join temptable b
on a.id = b.id;
insert into <main table> select * from temptable b
where not exists (select * from <main table>
where id = b.id
First option is easier and quick to implement but is not
appropriate in all situations. Second option will work in
all situations if you pk, but it could be little more
involved depending on number of columns you have.
My two cents...
>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>|||Ah, allright - does somebody have code examples I can use?
Best regards
Jakob
"Mary Lou Friend" <anonymous@.discussions.microsoft.com> skrev i en
meddelelse news:4d9301c402bf$698bdf80$a601280a@.phx.gbl...
> What I will do is create a temporary table in the same
> database with the data from the file.
> Next, I will write a script to (import) insert from the
> temp table to where you want the data to go.
> Mary
> >--Original Message--
> >Hey,
> >
> >1)
> >I'm doing a lot of importing (by DTS packages) from
> commaseparated files
> >into tables where i empty/truncate the table _before_
> import of ALL info in
> >the file.
> >
> >BUT - how do I import data from such a file by an UPDATE
> command...
> >
> >Meaning if the table has an ID col and a NAME col, and
> the file has an ID
> >col and a NAME col - then I would like to be able to
> UPDATE all NAME-cols in
> >the table by using the info from the file. The NAME in
> the file might have
> >changed, but the ID stays the same... Also, i new ID's
> are in the file, they
> >should be INSERTed into the table (+ the NAME).
> >
> >2)
> >I have been using ActiveX Data Trans for some imports -
> but it would be
> >great if I could perform SQL-taks (like above
> perhaps...? or is the
> >another way) - meaning: how do I make SQL call from
> within an ActiveX task?
> >
> >
> >Any help appreciated - Thanx!
> >
> >Best regards
> >
> >Jakob H. Heidelberg
> >Denmark
> >
> >
> >
> >
> >
> >
> >.
> >|||Thanx.
can you tell me how to create the TEMP table, and how to INSERT into it?
Also, when will the table "die"?
Thanx in advance
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:c29sc1$1rlv$1@.news.cybercity.dk...
> Hey,
> 1)
> I'm doing a lot of importing (by DTS packages) from commaseparated files
> into tables where i empty/truncate the table _before_ import of ALL info
in
> the file.
> BUT - how do I import data from such a file by an UPDATE command...
> Meaning if the table has an ID col and a NAME col, and the file has an ID
> col and a NAME col - then I would like to be able to UPDATE all NAME-cols
in
> the table by using the info from the file. The NAME in the file might have
> changed, but the ID stays the same... Also, i new ID's are in the file,
they
> should be INSERTed into the table (+ the NAME).
> 2)
> I have been using ActiveX Data Trans for some imports - but it would be
> great if I could perform SQL-taks (like above perhaps...? or is the
> another way) - meaning: how do I make SQL call from within an ActiveX
task?
>
> Any help appreciated - Thanx!
> Best regards
> Jakob H. Heidelberg
> Denmark
>
>
>