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
>
>
>