Hi

We have employee expenses data entry  application in our company. Developed in Dot net with SQL server.

Every employee will enter the entry once it is saved , the  same data we should write into  another  SQL database table.

And some instance , based on user choice we need to allow user to modify the data - I have to modify my table  - after saving in my table i have do the same update on the other SQL database table.

Can you please suggest  what is best way to  -access  other SQL database.

One such i got is : DTS package- to insert to new record. Schedule it for every half hour..

Thanks

INSERT INTO dbname.dbo.tbl (<cols>) VALUES (<values>)

UPDATE dbname.dbo.tbl SET col =<value>

If the data is moving on  to another database, does it serve for archiving/backup? Perhaps you can look into replication feature...

I have not done this in production, but I have done it in my test environment to access tables in different SQL Server.

Add a linked servers to a remote SQL Server. This example just happens to use a two part table name. Just remove the '\SQL2005' if yours in a one part name. (I think the correct terminology is that the instance name is the same as the server name)

USE master
GO
EXEC sp_addlinkedserver
    'FIDEV02MP\SQL2005',
    N'SQL Server'
GO

-- verify linked server was added
Select * from sys.servers

-- now try querying a db and table
select * from [FIDEV02MP\SQL2005].[DBNAME].[dbo].[TABLENAME]

you can also acheive this using MERGE( available in sql 2008 and above).

also, you are referring to DTS. DTS is legacy solution now..are you using sql 2000,  you can use ssis, if you want and it available in sql 2005. as said, there are multiple ways you can do this.. it find the below one a easy solution, since your are talking about only one table.. 

try this example..

create table test1(sno int primary key,sname varchar(20),scity varchar(20))
go
create table test2(sno int primary key,sname varchar(20),scity varchar(20))
go
insert into test1
values(1,'stan','atlanta'),(2,'william','newyork')
go
insert into test2
values(1,'robin','orlando')
go

--set this in you sql job and let it run for every half an hour
MERGE test2 t2
USING test1 t1
ON t2.sno = t1.sno
WHEN MATCHED THEN
  UPDATE
  SET t2.sname = t1.sname,t2.scity=t1.scity
WHEN NOT MATCHED by target THEN
Insert(sno,sname,scity)
values (sno,sname,scity);
go

--check this table and clean up
select * from test2 
go
drop table test1
go
drop table test2