Sync two tables

Hey folks,

I have a table in one server (SQL2K) that, among other columns, has a 
[datetime] column: ActionDate. In a linked server (also SQL2K), this same 
table exists which needs to be updated nightly with new records from the 
original since the last sync. I have the following:

insert into <synctable>
  select * from <linkedserver>.<dbase>.<table> a
    where a.ActionDate > (select max(ActionDate) from <synctable>)

This seems like it should work, but I thought I run it by gurus here for 
some pointers.

I'd also hope to do something similar, but without a linked server. Can DTS 
handle something like this?

Thanks in advanced.
 

0
Eddie
6/24/2010 6:11:52 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
968 Views

Similar Articles

[PageSpeed] 59

On Wed, 23 Jun 2010 23:11:52 -0700, "Eddie Pazz" <drpazz@hotmail.com>
wrote:

>Hey folks,
>
>I have a table in one server (SQL2K) that, among other columns, has a 
>[datetime] column: ActionDate. In a linked server (also SQL2K), this same 
>table exists which needs to be updated nightly with new records from the 
>original since the last sync. I have the following:
>
>insert into <synctable>
>  select * from <linkedserver>.<dbase>.<table> a
>    where a.ActionDate > (select max(ActionDate) from <synctable>)
>
>This seems like it should work, but I thought I run it by gurus here for 
>some pointers.
>
>I'd also hope to do something similar, but without a linked server. Can DTS 
>handle something like this?
>
>Thanks in advanced.
> 

This assumes actiondate is never updated. 

If it's just new records you're after then

insert into <synctable>
  select * from <linkedserver>.<dbase>.<table> a
    where not exists 
 (select 1 from <synctable> b where a.<primary key> = b.<primary key>)

will find records with new primary keys. 

Iain
0
Iain
6/24/2010 12:52:30 PM
Reply:

Similar Artilces:

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...

Sync subforms
Hi all I have two continuous subforms side by side on a main form which are therefore not directly linked with master and child (they have the same foreign key as the main form). I have found some code to keep the two subforms in sync which does work, but it is slightly slow and causes a kind of flickering form until it has made the sync (which repeats every time you move record). I want to keep them in sync so I can apply a conditional formatting to change the colour of some values if they don't equal some fields on the corresponding record on the other subform. Is the...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

PrintPreview
Hello, I developed a VC++/MFC SDI application. The program supports PrintPreview. However, I only want the user to go forward ("Next Page") and not backward ("Prev Page"). How do I hide the "Prev Page" and "Two Page" buttons? TIA, Jacques Hello All, I found a nice Print Preview replacement toolbar (with bitmap buttons) on codeguru. To hide the "Prev Page" and "Two Page" buttons, simply remove them from the CMyPreviewView::OnCreate(LPCREATESTRUCT lpCreateStruct) method. The article was written by Robin J. Leatherbarrow. Thank...

Exchange 2010 & Active Sync
Hi All, Running Exchange 2010. I have my phone (iphone 3gs) syncing with exchange 2010 perfectly. However, trying to sync a new plam pro (win mobile 6.1) and getting errors. I think it's the account as doing a test-ActiveSync-Connectivity gives the following: Any ideas? RunspaceId : 962cea80-32ae-4e60-a5f1-67ed96cab519 LocalSite : Default-First-Site-Name SecureAccess : True VirtualDirectoryName : Url : UrlType : Unknown Port : 0 ConnectionType...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

Write conflict error with ODBC link table
Hi, I have migrate my back end access tables to SQL Server. While editing data (ODBC link) from form, I receive Wirte conflict error 'The record has been changed by other user... Copying the change to the clipboard...'. The error allow me either copy the info to clipboard or drop change. In this case, how can I save my work to the table here? SF � "SF" <xyz@online.com.kh> ������ ��� ������ news:#F7F7OtcIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi, > > I have migrate my back end access tables to SQL Server. While editing data > (ODBC link) from form, I...

lost two months of received emails
Help I lost about 2 months of received emails. They are not in my deleted folder. I already tried the pst restore utility. Thanks ...

Sync Money with a Certificate of Deposit
I have a certificate of deposit CD, held at Bank of America, setup through money that I update manually. Recently, I setup Money 2006 to sync with Bank of America (California). And low and behold it downloaded this account. Unfortunately the downloaded account is a BANK account while the account that I created through MONEY is an INVESTMENT account. Is there any way to merge these two accounts and continue to get udpates on the balance? Really, I would like the account to remain an investment since Money regards CD accounts (wisely) as investments. -- Daniel ...

Exchange contact sync problems.
I have a problem with contact synconizations under Ex2003. We bought a (3rd party) modul for sync'ing contact between Axapta and Exchange. Only problem is, the address data is synced wrong and the company that sold us the modul, claims that the problem lies in our Exchange installation sins the module runs fine on other installations they've done. Fx: converting from Ax to Ex Zipcode is converted into the "provins" field cityname is converted into both city and zipcode field The other way zip -> cityname cityname just disapear The Ex2003 installation is a migration off...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

"How do I get rid of old records in an Pivot Table?
The spreadsheet gets copied each month to a new file and the data cleared out, BUT the selecetion in the Pivot Table still holds the data from Previous spreadsheets Debra Dalgleish has some techniques at: http://www.contextures.com/xlPivot04.html AyPee wrote: > > The spreadsheet gets copied each month to a new file and the data cleared > out, BUT the selecetion in the Pivot Table still holds the data from Previous > spreadsheets -- Dave Peterson The web-link was very helpful, thank you. I was about to reprogram and build my pivot tables to get rid of the obsolete item...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...

Pivot table help I think !
Hi All, I have a report I need to create which goes as follows. I have a data list of around 56k records. The rows contain this; category 1, category 2, category 3, call id and Date I've created a pivot table with Cat1, cat2, cat 3 on the row area, Count of Call ID in the data area and date in the column area (this is grouped by Month). I'm looking at the months of Mar, Apr and May. I want to sort it, in descending order, by the difference of 'Count of Call ID' there is between Mar and May. If I cannot perform the calculation in the pivot table, is there a way of ungroupi...

Two companies on SBS
Is it possible to implement CRM for two different companies on a Small Business Server? "sort of". Some things to consider: 1. Are both companies sharing the same Active Directory? Natively, not just using things like email - are their computers authenticating to the domain? 2. You could use the SAME instance of CRM, and create Business Units. If they want drastically different views, however, that could be difficult to set up for them. 3. If you want two separate instances of CRM - then you need an additional web/app server for the 2nd company. I belive that second...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Pivot Table toolbar
Every time I edit a pivot table (Excel 2000)the toolbar launches itself and I have to then re-anchor it manually - this is very irritating! Is there any way to stop this happening? "GeoffS" <crammond-smith@tiscali.co.uk> wrote in message news:39d201c355a8$a7f788b0$a001280a@phx.gbl... > Every time I edit a pivot table (Excel 2000)the toolbar > launches itself and I have to then re-anchor it manually - > this is very irritating! > > Is there any way to stop this happening? I use Excel97 but it may be the same. You can have the toolbar on all the time, positio...

Trouble syncing playlists to Mp3 player
Hi there, I have a Coby MP705- 8GB that I am using with WMP 11. I am currently having trouble synching playlists from Itunes that are on my WMP to my Coby which had previously worked in the past. I manually go through the steps to add the playlists and watch as each song in every playlist is converted onto the Coby. This also results in duplicate songs on the Coby which I just delete by hand one by one (a little annoying). When the sync is complete and I click on the playlists for the Coby, I receive the message, "There are no playlists on this device." Not sure if I a...