insert and update stratergy in MS DTS

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

Import/Export (DTS) and Replication (2000)

insert and update stratergy in MS DTS

Author

Topic

nrenuka
Starting Member

1 Post

Posted - 2005-04-21 : 02:35:35

Hi All,

I am using Data Driven Query task for insert and update into the target table.

the problem it is not updating and exactly not inserting. it is appending to the existing data.

what is the solution for this?

renuka

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-25 : 02:08:27

What are you trying to do exactly?
You want to re-insert all the datas and not append?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

ammy
Starting Member

7 Posts

Posted - 2005-04-27 : 00:12:11

Hi Karuna,
i am trying to insert the data from 2 source file one is excel and other is text file into sqlserver database that is into a single table using MS DTS.

whenever any DML operations done on the sources files, it has to create a log file with transaction type as insert(I), Delete(D), and Update(U); and also the date, and other columns.

when insert has been done in the source file it has to fire the below trigger so that it can maintain the log table of new inserts with transaction type (I). same for delete and update.

CREATE TRIGGER trg_customers_i ON customer_Stage1 for INSERT
AS
INSERT INTO Customer_Stage1_log(tran_type, CustomerID, CompanyName,Address,City,Country)
SELECT 'I' AS tran_type, CustomerID, CompanyName,Address,City,Country
FROM insertedThe problem facing by me is:
1) when i run the DTS package, data is appending into the sqlserver table not inserting.
2)when you insert data into the sqlserver table, it should maintain the log table which shows the new inserts. this is also not happening.
3) if i manually enter the data into sqlserver table manully with out DTS then the trigger fires, but when i load data from two sources into sqlserver table trigger doesnt fires.

please check the code where i am going wrong.

CREATE TABLE [dbo].[Customer_Stage1] (
[CustomerKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nvarchar] (255) NULL ,
[CompanyName] [nvarchar] (255) NULL ,
[Address] [nvarchar] (255) NULL ,
[City] [nvarchar] (255) NULL ,
[Country] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customer_Stage1_log] (
lsn INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
log_date DATETIME NOT NULL DEFAULT GETDATE(),
tran_type CHAR(1) NOT NULL CHECK(tran_type IN('I', 'U', 'D')),
CustomerKey int NOT NULL ,
CustomerID nvarchar (255) NULL ,
CompanyName nvarchar (255) NULL ,
Address nvarchar (255) NULL ,
City nvarchar (255) NULL ,
Country nvarchar (255) NULL ,
namechg BIT NULL,
Addresschg BIT Null,
Citychg BIT Null,
Countrychg BIT NULL )
GO

drop trigger trg_customers_i
go
CREATE TRIGGER trg_customers_i ON customer_Stage1 for INSERT
AS
INSERT INTO Customer_Stage1_log(tran_type, CustomerID, CompanyName,Address,City,Country)
SELECT 'I' AS tran_type, CustomerID, CompanyName,Address,City,Country
FROM inserted
go
drop trigger trg_customers_d
go
CREATE TRIGGER trg_customers_d ON customer_Stage1 FOR DELETE
AS

INSERT INTO Customer_Stage1_log(tran_type, CustomerID, CompanyName,Address,City,Country)
SELECT 'D' AS tran_type, CustomerID, CompanyName,Address,City,Country
FROM deleted
GO

Thanks
Renuka

quote:
--------------------------------------------------------------------------------
Originally posted by karuna

What are you trying to do exactly?
You want to re-insert all the datas and not append?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

--------------------------------------------------------------------------------

Close Window

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-27 : 03:09:06

quote:
Originally posted by ammy

i am trying to insert the data from 2 source file one is excel and other is text file into sqlserver database that is into a single table using MS DTS.

If you are importing the files using a Data Transformation Task, then the trigger might not be fired. AFAIK Data Transformation Task uses bcp try unchecking use fast load in the options tab.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources