Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 Script Help

Posted on 2013-10-29
12
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 66

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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