Solved

SQL Server 2005 Script Help

Posted on 2013-10-29
12
412 Views
Last Modified: 2013-10-31
I have two tables that have similar information but in two different environments and one is current, but the other isn’t; however, like to use the records on one by scheduling a job to always update the other table using the information from the table that is current.

How you achieve this in SQL Server 2005 since it has no merge statement? Some help here.

See my columns (both tables have same columns)

SELECT [subsys]
      ,[subkey]
      ,[seq]
      ,[pos_code]
      ,[name]
      ,[phone]
      ,[ext]
      ,[eff_dt]
      ,[exp_dt]
      ,[crby]
      ,[crdt]
      ,[mtby]
      ,[mtdt]
      ,[email]
FROM [Mydb].[dbo].[mytable_d1]
0
Comment
Question by:Favorable
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39610049
>from the table that is current.
Help us out with the timing here.  
Does 'current' mean every hour, every day, ever since the last time data was inserted into the backup table then deleted from the 'current' table....

If data is moved from 'current' to 'backup' is all data deleted from 'current' or does it still live?  Meaning, can changes be both inserts (new rows) and updates (changes to existing rows).

<Insert whine of choice here...>
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39610069
Is mtdt the field that changes if there is a new change? Like a last changed date time?

What do you mean by two different environments?

Are they both on the same database on the same SQL Server but in 2 different tables?

Or are the same server but different databases?

Or different server/table/database/everything? Different SQL versions? Different database altogether (Oracle, MySQL, etc?)?

What you want to do is possible even if the last item is true, its just a different way to get the job done. The easiest obviously is same database or at least same server, different tables. But with OpenQuery and/or Linked Server we can overcome that.

Please post more info about your environment, and the definitions of your tables.


Just as a quick example, lets say that they are on two different servers, you could do this:

CREATE #temp (columns like your source table)

INSERT #temp
SELECT * FROM OPENROWSET('server','database','userid','userpw',' SELECT * from source_table where modified_date >= '''+@last_time_ran + '''' )

-- this is for updating records
Update d
FROM dest_table d
INNER JOIN #temp as t
on d.key_columns = t.key_columns

-- this is for inserting new records
INSERT dest_Table
FROM #temp  as t
WHERE not exists (
select 1 from dest_table as d where t.key_columns = d.key_columns )

deleting is going to be pretty tricky, maybe not possible. Maybe not needed.
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39610081
also on that not exists, that can be huge resource hog if the table has a huge number of rows without proper indexing. You should try to use a date-appropriate index or create one if you know that date column X would have to be > time Y for it to exist or not (like maybe based on the last time ran).
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Favorable
ID: 39610144
This will be scheduled in a nightly job for once daily.

It should be able to update the table with whatever data change from the table that is current.
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39610227
Is there a primary key in the table that you can use to check the changed or newly added data?
0
 

Author Comment

by:Favorable
ID: 39610534
These are the key columns

     [subsys]
      ,[subkey]
      ,[seq]
0
 

Author Comment

by:Favorable
ID: 39610541
Same named tables/columns in two different SQL Server environments, but same version of SQL Server 2005.
0
 
LVL 8

Accepted Solution

by:
virtuadept earned 500 total points
ID: 39612231
Here is a more detailed example than the one I posted above, you will need to fill in some of the blanks like the actual table name, db name, server, etc.

-- STEP 1 - make a temp table to store the results from source table
SELECT [subsys]
      ,[subkey]
      ,[seq]
      ,[pos_code]
      ,[name]
      ,[phone]
      ,[ext]
      ,[eff_dt]
      ,[exp_dt]
      ,[crby]
      ,[crdt]
      ,[mtby]
      ,[mtdt]
      ,[email]
INTO #temp 
FROM [Mydb].[dbo].[mytable_d1]
WHERE 1=2

DECLARE @last_time_ran DATETIME
SET @last_time_ran = DATEADD(HOUR,-25,GETDATE())  
-- or however you want to keep track, this would be if you run 
-- the job about the same time once a day. -25 instead of -24 in case of a bit of overlap
-- it should not mess anything up. 
-- It would be very helpful if the table had at least a nonclustered index on mtdt.

-- Step 2, Use OpenRowSet to populate the temp table from the source server.
INSERT #temp
SELECT * FROM OPENROWSET('SourceServer','Mydb','userid','userpw',' SELECT * from  [Mydb].[dbo].[mytable_d1] where mtdt >= '''+ CONVERT(VARCHAR,@last_time_ran,121) + '''' )

-- note convert datetime to varchar with 121 is 24h yyyy-mm-dd hh:mm:ss:fff 
-- so that you can get down to miliseconds. This assumes mtdt is 
-- some kind of last time of record change column on the record. 
-- if that is not what mtdt is, then you need to figure out some other way to 
-- build a WHERE clause to pick up recent changed records.

-- STEP 3 - for updating records
Update d
SET [pos_code] = t.[pos_code] 
      ,[name] = t.[name]
      ,[phone] = t.[phone]
      ,[ext] = t.[ext]
      ,[eff_dt] = t.[eff_dt]
      ,[exp_dt] = t.[exp_dt]
      ,[crby] = t.[crby]
      ,[crdt] = t.[crdt]
      ,[mtby] = t.[mtby]
      ,[mtdt] = t.[mtdt]
      ,[email] = t.[email]
FROM [Mydb].[dbo].[mytable_d1] as d
INNER JOIN #temp as t
on d.[subsys] = t.[subsys]
and d.[subkey] = t.[subkey]
and d.[seq] = t.[seq]

-- STEP 4 - for inserting new records
INSERT [Mydb].[dbo].[mytable_d1]
FROM #temp  as t
WHERE not exists (
select 1 from [Mydb].[dbo].[mytable_d1] as d 
WHERE d.[subsys] = t.[subsys]
and d.[subkey] = t.[subkey]
and d.[seq] = t.[seq]
)

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 39612242
>> Same named tables/columns in two different SQL Server environments,

You mean two different servers?
0
 

Author Comment

by:Favorable
ID: 39612417
One on server A and one on server B
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39612660
virtuadept has already posted a script. Please check if you have any issues with that before we proceed.
0
 

Author Closing Comment

by:Favorable
ID: 39615225
thank you very much!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question