Inserting Table to a Linked Server

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)

Inserting Table to a Linked Server

Author

Topic

marileng
Starting Member

28 Posts

Posted - 2004-05-30 : 22:16:34

I need to insert,update and delete
a table on the linked server in comparison to the table here in my server.

Here is the DDL

CREATE TABLE [dbo].[account_contact2] (
[account_contact_id] [int] NOT NULL ,
[account_id] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[acct_location_id] [int] NULL ,
[active] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first_name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middle_name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nickname] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[salutation] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dear] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[department] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pager] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ext] [int] NULL ,
[email] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[birthday_dt] [datetime] NULL ,
[home_phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[change_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reference_note] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[change_dt] [datetime] NULL ,
[change_uid] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sync_uid] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sync_dt] [datetime] NULL ,
[suffix] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assistant_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[assistant_phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manager_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company_phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[password] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[binChkSum] AS (binary_checksum([account_id],[acct_location_id],[active],[assistant_name],[assistant_phone],[birthday_dt],[cell_phone],[change_dt],[change_flag],[change_uid],[company_phone],[dear],[department],[email],[ext],[fax],[first_name],[home_phone],[last_name],[manager_name],[middle_name],[nickname],[notes],[pager],[password],[phone],[reference_note],[salutation],[suffix],[sync_dt],[sync_uid],[title],[type]))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[account_contact] WITH NOCHECK ADD
CONSTRAINT [IX_account_contact] UNIQUE CLUSTERED
(
[account_contact_id]
) ON [PRIMARY]
GO

I need to compare it if there is an Update, Insert and delete with the table in my server then update all the changes in the linked server.

It has 3M records now how am I gonna do it?
A simple select statement takes up to 2 minutes how can I make it faster?

Kristen
Test

22859 Posts

Posted - 2004-05-31 : 01:46:42

Do you want to do this once in a while, or all the time?

Presumably for "all the time" you could put a TRIGGER on the table that copied to/deleted from linked server.

Or you could use REPLICATION to get the data to the linked server?

For once-in-a-while could you add an "UpdateDate" column to the table and put GetDate() in there whenever insert/update? That would allow you to remember the date/time of the last transfer, and then insert/update anything after that date (or perhaps "on or after" that date to be safe). That doesn't cover deletion though ... so you either have another table to remember the IDs of those (probably maintained by a DELETE TRIGGER, but could be in your application SQL code).

On that theme you could have a table of ID's which was updated, by TRIGGER, whenever a record was changed/deleted. Then your Batch process could use that table as the "work to do", deleting IDs once they were transfered.

Otherwise I think you need a complete WHERE clause of all columns to work out which rows have changed:


WHERE
(Table1.MyColumnA <> Table2.MyColumnA 
 OR (Table1.MyColumnA IS NULL 
     AND Table2.MyColumnA IS NOT NULL) 
 OR (Table1.MyColumnA IS NOT NULL 
     AND Table2.MyColumnA IS NULL)
) OR
(Table1.MyColumnB ...

which, as you say, is gonna take a while .... and won't work for your TEXT column

Kristen

marileng
Starting Member

28 Posts

Posted - 2004-05-31 : 02:16:53

We do it once in a while every morning

Actually there are 30 tables that needs to be updated in the linked server.

The actual setup is that they truncate all the table in the linked server then insert all the tables in the prod server to the linked server.

The process takes up about 2 hours to be able to complete the database.

I created a script and put on checksum field on all the table and compare the checksum field.

Then Update all the checksum that do not match.

Then Insert the data that does not exist yet on the linked server.

Then delete the data that was deleted on the prod server
here is the sample code :

delete table1 FROM table1

inner join
(select table2.field1,table2.field2 FROM table2 left outer join
table3 on table2.field1= table3.field1 and table2.field2=table3.field2 WHERE
(table3.field1 IS NULL and table3.field2 is null)) x

on table1.field1= x.field1 and table1.field2=x.field2

1. insert to table3 - records in table1, not in table2

2. update table3 - records in table1, in table2, not equal checksum

3. delete table3 - records not in table1, in table2

delete table3
FROM table3
inner join
(select TABLE2.field1,TABLE2.field2,TABLE2.field3 FROM TABLE2 left outer join
TABLE1 on TABLE2.field1= TABLE1.field1 and TABLE2.field2=TABLE1.field2 AND
TABLE2.field3= TABLE1.field3
WHERE
(TABLE1.field1 IS NULL and TABLE1.field2 is null AND TABLE1.field3 IS NULL)) x

on table3.field1= x.field1 and table3.field2=x.field2

update table3 set table3.fIEld1=y.fIEld1, table3.fIELd2=y.fIELd2,table3.fIELd3=y.fIELd3

from table3 inner join

(select a.fIELd1,a.fIELd2 ,a.fIELd3 from table1 a inner join table2 b
on a.fIELd1=b.fIELd1 and a.fIELd2=b.fIELd2
AND a.fIELd3=b.fIELd3
where a.chksum<>b.chksum) y

on table3.fIELd1=y.fIELd1 and table3.fIELd2=y.fIELd2
AND table3.fIELd3=y.fIELd3

insert into table3

select TABLE1.field1,TABLE1.field2,TABLE1.field3 FROM TABLE1 left outer join
TABLE2 on TABLE2.field1= TABLE1.field1 and TABLE2.field2=TABLE1.field2 AND
TABLE2.field3= TABLE1.field3
WHERE
(TABLE2.field1 IS NULL and TABLE2.field2 is null AND TABLE2.field3 IS NULL)

My problem was it took much time when i run my SP it lasted for almost 24 hours.

Pls help me

Kristen
Test

22859 Posts

Posted - 2004-05-31 : 03:17:56

>>We do it once in a while every morning

OK, so a batch job called for ...

>>The actual setup is that they truncate all the table in the linked server then insert all the tables in the prod server to the linked server.

Using DTS? If they are doing


TRUNCATE DestinationServer.Database.dbo.table
(or DELETE)
INSERT INTO DestinationServer.Database.dbo.table
    SELECT *
    FROM   dbo.LocalTable

this will be horrendously slow because it will treat all rows in the table as a single batch. However, using DTS (or some other BULK COPY method) would be much quicker.

(If the 2 hours is interfering with the running of the server you could look at EXPORTING the data, transferring the exported file to the Linked Server, and then using Bulk Copy program (BCP) to import it at the other end - using the same DELETE EVERYTHING approach you currently do. The EXPORT will take a very short amount of time, relative to the current method).

>>The process takes up about 2 hours to be able to complete the database.

Is that so awful? Do you have slack time (e.g. during the night) when this can be scheduled?

>>I created a script and put on checksum field on all the table and compare the checksum field.
>>Then Update all the checksum that do not match.

Not a bad idea. Presumably there is a theoretical [statistically very small!] possibility that two, different, records will have the same checksum?

The real problem I perceive with this approach is that EVERY RECORD must be compared with the data on the Linked Server. That is very slow (relative to comparisons with data on the local server). It would be much better to work out what "work" has to be done on the local server WITHOUT needing to check records against the remote server.

Hence my suggestion of having an UPDATE DATE on each row (set by a TRIGGER if you like, otherwise in your application logic). Then you can just SELECT all rows with UPDATE date since the last Batch Run to transfer. (You do have to also have some way of "remembering" deleted records. Probably a table containing the Primary keys of deleted records, but you could also have a flag in the record so it is MARKED as deleted, but not physically deleted until the update to remote sever has been done; downside with this is that you application will have to exclude records marked as deleted; you could also resort to doing a NOT EXISTS between the Local and Remote servers).

FWIW we have a GUID on all records that get involved in this kind of pushing-around. We have a table of "deleted GUIDs" (i.e. ONE table used by ALL tables that need to monitor deletions) and we use that to control the deletion process.

Only other observation is that the JOINs and NESTED SELECTs you have between the tables looks more complicated than having an EXISTS / NOT EXISTS type construction, but you may have a reason for that.

Also I don't understand why 3 tables seem to be involved.

Also you should probably do the DELETEs in "reverse foreign key order" and the Insert/Updates in "ascending foreign key order". I think you also need to do the UPDATE for a table immediately followed by the INSERT for that table, and then move on to the next table. (Rather than ALL updates and then ALL inserts - but I can't quite make my mind up on that one!)

For example: DELETE Order Items BEFORE Order Headers; but UPDATE & INSERT Order Headers before Order Items

You may still get foreign key breakage - but just running the script again should sort that out - dependant data will get created on the first run and then be available to the second (or possibly third ...!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources