Solved

SQL Server 2005 Script Help

Posted on 2013-10-29
12
408 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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Favorable
Comment Utility
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
Comment Utility
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
Comment Utility
These are the key columns

     [subsys]
      ,[subkey]
      ,[seq]
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Favorable
Comment Utility
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
Comment Utility
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
Comment Utility
>> Same named tables/columns in two different SQL Server environments,

You mean two different servers?
0
 

Author Comment

by:Favorable
Comment Utility
One on server A and one on server B
0
 
LVL 40

Expert Comment

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

Author Closing Comment

by:Favorable
Comment Utility
thank you very much!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now