Solved

SQL Server 2005 Script Help

Posted on 2013-10-29
12
413 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 41

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 41

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

733 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