Updating from a very large external table

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

Transact-SQL (2000)

Updating from a very large external table

Author

Topic

rcurrey
Starting Member

30 Posts

Posted - 2007-02-23 : 09:14:57

create table promo (
id float not null
,fname varchar(30)
,lname varchar(30)
,address varchar(50)
,item float
,qty int)

This table is inserted with id, item, and qty filled out. The personal information that goes with the id resides in another db on a separate server. We need to update this table using the external table's information. The PROMO table will have several hundred rows per day being inserted. The table with the personal info has over 11,000,000 rows. We only use openrowset (no linked servers allowed).

Looking for suggestions on how to best accomplish this. Any help would be appreciated.

Thanks,
Rich

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-23 : 10:56:06

quote:
Originally posted by rcurrey

create table promo (
id float not null
,fname varchar(30)
,lname varchar(30)
,address varchar(50)
,item float
,qty int)

This table is inserted with id, item, and qty filled out. The personal information that goes with the id resides in another db on a separate server. We need to update this table using the external table's information. The PROMO table will have several hundred rows per day being inserted. The table with the personal info has over 11,000,000 rows. We only use openrowset (no linked servers allowed).

Looking for suggestions on how to best accomplish this. Any help would be appreciated.

Thanks,
Rich

Have you considered replication?

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

rcurrey
Starting Member

30 Posts

Posted - 2007-02-23 : 12:20:38

Yes. We have considered it, but we already replicate this table to 4 different servers. Really don't want to do it to a fifth if I can help it.

Thanks,
Rich

Kristen
Test

22859 Posts

Posted - 2007-02-24 : 05:14:31

So, just to make sure I understand:

You want to insert id, item, and qty into [promo], and then populate fname ,lname and address from a table in another database that contains 11M rows?

There are only a few HUNDRED inserts into [promo] each day.

OK, I would use OPENQUERY for this. Depends a bit on whether you need the information as-you-insert, or whether it could come along as a Batch process.

For remote connection like this I always use a Batch - that way if the other server is offline your application still works!

So I would add a BIT column called HaveIGotDataFromOtherServer (you can use a shorter name if you like!!) and then have a Batch process that populates those records and clears the BIT field.

I would code it something like this:


DECLARE @strIDs varchar(1000),
        @strSQL varchar(8000)

SELECT TOP 100 @strIDs = COALESCE(@strIDs+',', '') + CONVERT(varchar(20), id)
FROM dbo.promo
WHERE HaveIGotDataFromOtherServer = 0
ORDER BY id -- If more than 100 available do earlier IDs first

SELECT @strSQL = '
SELECT	*
INTO	##TEMP_PROMO
FROM	OPENQUERY(MyRemoteServer, 
''SELECT id, fname ,lname, address FROM dbo.RemotePersonalInfoTable
WHERE id IN (''''' + @strIDs + ''''')'' '

PRINT @strSQL -- Debug only!

EXEC (@strSQL) -- Transfer the data to local table

UPDATE U
SET fname = T.fname,
    lname = T.lname,
    address = T.address,
    HaveIGotDataFromOtherServer = 1  -- Mark as "Processed"
FROM dbo.prompt AS U
     JOIN ##TEMP_PROMO AS T
        ON T.id = U.id
WHERE U.HaveIGotDataFromOtherServer = 0 -- Belt&Braces in case something else got there first

DROP TABLE ##TEMP_PROMO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources