DTS script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS script

Author

Topic

sameerv
Starting Member

29 Posts

Posted - 2002-07-19 : 07:09:30

Hi guys,
I need you to help me out ..

I have a delimited text file containing data, each row has 3 fields.delimited by "|" .

BankID , Bank Name , NewBank(yes/no)

sample data :
AUB|ANAND URBAN CO-OPERATIVE BANK LTD.|Y
AUCB|AKOLA URBAN CO-OP BANK LIMITED|N
AUCBL|ALMODA URBAN CO-OPERATIVE BANK LIMITED|N
AUCOOP|ANDARSUL URBAN CO-OP BANK LTD.|Y

I have a table called BankLog having 7 fields.:

1. BankID
2. Bank Name
3. Bank Address
4. Bank Phone
5. Fax
6. NewBank(yes/no)
7. EMailID

PART A:
Using DTS script(not wizard) I need to import the text data into the fields 1,2 and 6 respectively of the table. I should invoke the DTS package from a St Proc.

PART B:
Also, assuming that the Bank phone is a not null field, I will have to insert a default value into this field simultaneously while importing each record.

I'd be grateful if you could help me out.

PS: Also, if you have the time, can you give me a solution for accomplishing the above using Bulk Insert. Does Bulk Insert allow conditional Inserts.

Thanx Guys for sparing the time.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-19 : 07:18:43

sameerv-

Have you looked up DTS and BULK INSERT in Books Online? There's excellent documentation on how to build data trasformation tasks, and also how to use format files with BULK INSERT to re-map the imported columns (also look under "bcp" and "format file")

As far as importing a default phone number, you can't do this with BULK INSERT in a single operation, unless you write an INSERT trigger that updates all null phone numbers with the default value:

CREATE TRIGGER DefaultPhone ON myTable FOR INSERT, UPDATE AS
UPDATE myTable
SET PhoneNumber='111-111-1111'
FROM myTable INNER JOIN inserted ON myTable.BankID=inserted.BankID
WHERE inserted.PhoneNumber IS NULL

If you do it this way, you need to use the FIRE_TRIGGERS option of the BULK_INSERT command.

sameerv
Starting Member

29 Posts

Posted - 2002-07-19 : 10:50:33

Hi Rob,
Thanx for the advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources