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

Import/Export (DTS) and Replication (2000)

update data

Author

Topic

nurul iza
Starting Member

17 Posts

Posted - 2006-02-19 : 21:52:00

hi all...
i'm nurul...i have one project that use SQL server 2000 and asp. But i have a problem how to update the data into my own database from another server.For example: i need to copy the data from server A into server B.

I have already create a package at server A using DTS.In first time, i can copy all the data into my database using this Package.Successful...But if in the second time, i only need to copy the data in this day without copy everything from server...how can i do it??any idea...because it will make my server hang if i still copy all thing....

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-19 : 22:38:22

you have any identifiers which says that this data was transferred earlier?

Karunakaran

nurul iza
Starting Member

17 Posts

Posted - 2006-02-19 : 23:27:43

i just try using DTS package and it will copy all the data into my database but it will take a long time to transfer.I'm not sure whether have any way that easily than this. If u have any idea can u told me....

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-20 : 00:36:14

Hi,
You can create a Linked Server between Server1 and Server2.
Use Openquery to insert only those records which doesnot exist in Server2

Check BOL for more Info on
OpenQuery
OpenRowSet

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 02:23:38

hi,
thanks 4 idea but i'm not sure how to implement it. I use a stored procedure in SQL so how can i update using a stored procedure or maybe have any idea that can update using DTS package.....can u give example using this data:

myserver:A
table name:service
database name:business

server that i use to get the data:B
table name:service1
database name:business1
fields that i need:date,business_group,impact

so i need to update all this field into my my database(business)...only update the latest data from database business1 in server B....

<thanks>

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-20 : 02:45:53

But how do you identify which is the latest data? By comparing with other database? or do you have a column which says these records were uploaded to other server?

Thanks

Karunakaran

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 02:54:57

In this case:i need to transfer the Date from server B into my server (A).so now i need to specified using this Date. That's mean i only need to get the data for today into my database. If i still transfer all the data into my database, it will hang my server.How should i do??

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-02-20 : 03:20:36

quote:
Originally posted by nurul iza

In this case:i need to transfer the Date from server B into my server (A).so now i need to specified using this Date. That's mean i only need to get the data for today into my database. If i still transfer all the data into my database, it will hang my server.How should i do??

This is will work?

Select date1,business_group,impact from table1 where Datediff(day,date1,getdate()) = 0

Thanks

Karunakaran

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 03:56:45

karunakaran...
it's ur coding will update the data from server B into server A? where should i create this query??u didn't mension the server name so how this coding will help me?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-20 : 04:23:56

1. Setup link server to ServerB at ServerA. Example : Run this at ServerA to create a link to ServerB

    exec sp_addlinkedserver 'SERVERB', '', 'SQLOLEDB', 'SERVERB'

Refer to Books Online for details syntax for sp_addlinkedserver

2. To retrieve from table from ServerB into table in ServerA, run this in ServerA

INSERT INTO service(date, business_group, impact)
SELECT date, business_group, impact
FROM   SERVERB.business1.dbo.service1 b
WHERE  b.date >= dateadd(day, 0, datediff(day, 0, getdate())  -- Greater and equal to today's date
AND    b.date <  dateadd(day, 1, datediff(day, 0, getdate())) -- Less than tomorrow's date

quote:
i only need to get the data for today into my database

Are you going to run this daily ? When you are going to run this ? At 23:59:59 ? How are you going to ensure that all's today's data falls within the selection criteria ?

You might want to consider :

WHERE  b.date >= dateadd(day, -1, datediff(day, 0, getdate())  -- include yesterday's date
AND    b.date <  dateadd(day, 1, datediff(day, 0, getdate())) -- Less than tomorrow's date

Besider this, you will also need to add checking existance of record in service table. Something like

where not exists (select * from service x where x.some_primary_key = b.some_primary_key)

----------------------------------
'KH'
Time is always against us

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 19:17:59

thanks khtan...
But i have a question:
1) my date is in Unix Type (exp:1010012101) so can i use this statament WHERE b.date >= dateadd(day, -1, datediff(day, 0, getdate()) to get my data?

2) i need to run daily so it's i must run all this statement in my queries every day?or it will automatically updated into my database??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-20 : 20:11:37

"1) my date is in Unix Type (exp:1010012101) so can i use this statament WHERE b.date >= dateadd(day, -1, datediff(day, 0, getdate()) to get my data?"
No. you will have to convert current date to your Unix Type format

"i need to run daily so it's i must run all this statement in my queries every day?or it will automatically updated into my database??"
You will need to run it daily or the frequency that you required. You can setup a scheduled job to do this

----------------------------------
'KH'
Time is always against us

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 20:21:35

khtan...
In my project i use this function to convert the unix type to human type..DATEADD(s, Create_Date, '19700101')...It's can work...Now i need to update the data from server to my database using the date...and the date in server still in Unix Type..How should i do to get the data...can u give me example...

<thanks>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-20 : 21:24:46

Something like this

INSERT INTO service(date, business_group, impact)
SELECT dateadd(second, date, '19700101'), business_group, impact
FROM   SERVERB.business1.dbo.service1 b
WHERE  b.date >= datediff(second, '19700101', dateadd(day, 0, datediff(day, 0, getdate()))) -- Greater and equal to today's date
AND    b.date <  datediff(second, '19700101', dateadd(day, 1, datediff(day, 0, getdate()))) -- Less than tomorrow's date

----------------------------------
'KH'
Time is always against us

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 21:29:58

thanks khtan...

i get an idea...u r good programmer:)

<Nurul Iza>

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 22:16:14

khtan...

i have a last step here...how can i create a schedule for this job?
to make it run daily...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-20 : 22:31:27

"i have a last step here...how can i create a schedule for this job?
to make it run daily..."
That's good. Last Step

After that is test, test, test and debug, debug, debug.
Use Enterprise Manager, Management - SQL Server Agent - Jobs
Right click to create New Job.

It is prefered to create a stored procedure for your query and execute the stored procedure in the Job Step.

----------------------------------
'KH'
Time is always against us

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 22:49:34

khtan...thanks for ur support..
another Q:
1) it's i should create this schedule at my server(A) or another server(B)?
2) i need to develop one web site that can view the graph and i have already finish the stored procedure to get the data before view the graph using asp code. so can i paste all my stored procedure into this 'step'?and it will run together right?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-20 : 22:58:55

1) depends on where is your SP. If you create in SERVERA, than you should be SELECT from SERVERB and INSERT into SERVERA. So you should schedule in SERVERA.

2) if you have more than one stored procedures, just execute in under the 'step'

exec yoursp1
exec yoursp2

----------------------------------
'KH'
It is inevitable

nurul iza
Starting Member

17 Posts

Posted - 2006-02-20 : 23:02:46

thanks a lot khtan...

i learn something new here..it'll help me to finish this project successfull..:)

<thanks>

nurul iza
Starting Member

17 Posts

Posted - 2006-02-21 : 19:53:14

hi khtan...
another question,if the server name have _,/,- for example "Service-Desk_2" where should i change to implement in this coding...

FROM Service-Desk_2.business1.dbo.service1 b

<thanks>

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources