Using SQL to append records to a table from a flat file using DTS

[blackd77]

What I would LIKE to do is noted in the subject line. What I'm finding
is that "edit SQL" appears to only be an option if I am creating a
table. If I select "append to" the option to edit SQL shades itself as
unavailable.

The reason I'd like this is that there is a datum in the flat file that
indicates whether that record should be appended to that table noted
above. There are other ways of dealing with this "problem" but it would
be nice to be able to control it using SQL, in the DTS import/export
wizard.

If the source of my data is an SQL table, I can generate an SQL query
to specify what fields to import in an append, to check for existing
values, etc...

Is there a way around this? I can reserve a table for data transfers,
regularly overwrite it with new data from text file inputs, and use SQL
to insert select fields from that transfer table to other database
tables. (From this "transfer" table, data needs to be inserted into
four separate tables in our database).

I hope this is clear. If it CAN'T be done this way, it's okay...just a
little ugly with the need to re-create the transfer table.

[blackd77]

This system is running MS SQL server 2000.
[e-mail] wrote:

What I would LIKE to do is noted in the subject line. What I'm finding
is that "edit SQL" appears to only be an option if I am creating a
table. If I select "append to" the option to edit SQL shades itself as
unavailable.

The reason I'd like this is that there is a datum in the flat file that
indicates whether that record should be appended to that table noted
above. There are other ways of dealing with this "problem" but it would
be nice to be able to control it using SQL, in the DTS import/export
wizard.

If the source of my data is an SQL table, I can generate an SQL query
to specify what fields to import in an append, to check for existing
values, etc...

Is there a way around this? I can reserve a table for data transfers,
regularly overwrite it with new data from text file inputs, and use SQL
to insert select fields from that transfer table to other database
tables. (From this "transfer" table, data needs to be inserted into
four separate tables in our database).

I hope this is clear. If it CAN'T be done this way, it's okay...just a
little ugly with the need to re-create the transfer table.

[John Bell]

Hi

I suggest that you use the wizard to create the DTS package initially and
then add workflow that will optionally truncate the destination table
depending on the values initially read from the file and then re-read the
file to insert the data. To read the same row multiple times see
http://www.sqldts.com/default.aspx?266 and to skip a task see
http://www.sqldts.com/default.aspx?214

John

<bl******@hotmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...

This system is running MS SQL server 2000.
[e-mail] wrote:

>What I would LIKE to do is noted in the subject line. What I'm finding
is that "edit SQL" appears to only be an option if I am creating a
table. If I select "append to" the option to edit SQL shades itself as
unavailable.

The reason I'd like this is that there is a datum in the flat file that
indicates whether that record should be appended to that table noted
above. There are other ways of dealing with this "problem" but it would
be nice to be able to control it using SQL, in the DTS import/export
wizard.

If the source of my data is an SQL table, I can generate an SQL query
to specify what fields to import in an append, to check for existing
values, etc...

Is there a way around this? I can reserve a table for data transfers,
regularly overwrite it with new data from text file inputs, and use SQL
to insert select fields from that transfer table to other database
tables. (From this "transfer" table, data needs to be inserted into
four separate tables in our database).

I hope this is clear. If it CAN'T be done this way, it's okay...just a
little ugly with the need to re-create the transfer table.

[blackd77]

Thanks! I haven't a chance to try this yet, but I will soon.
John Bell wrote:

Hi

I suggest that you use the wizard to create the DTS package initially and
then add workflow that will optionally truncate the destination table
depending on the values initially read from the file and then re-read the
file to insert the data. To read the same row multiple times see
http://www.sqldts.com/default.aspx?266 and to skip a task see
http://www.sqldts.com/default.aspx?214

John

<bl******@hotmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...

This system is running MS SQL server 2000.
[e-mail] wrote:

What I would LIKE to do is noted in the subject line. What I'm finding
is that "edit SQL" appears to only be an option if I am creating a
table. If I select "append to" the option to edit SQL shades itself as
unavailable.

The reason I'd like this is that there is a datum in the flat file that
indicates whether that record should be appended to that table noted
above. There are other ways of dealing with this "problem" but it would
be nice to be able to control it using SQL, in the DTS import/export
wizard.

If the source of my data is an SQL table, I can generate an SQL query
to specify what fields to import in an append, to check for existing
values, etc...

Is there a way around this? I can reserve a table for data transfers,
regularly overwrite it with new data from text file inputs, and use SQL
to insert select fields from that transfer table to other database
tables. (From this "transfer" table, data needs to be inserted into
four separate tables in our database).

I hope this is clear. If it CAN'T be done this way, it's okay...just a
little ugly with the need to re-create the transfer table.