Solved

SQL Server 2005 Script Help

Posted on 2013-10-29
12
410 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need some help to cast ntext to nvarchar SQL 2000 7 34
[SQL server / powershell] bulk delete table from CSV 8 33
VB.NET 2008 - SQL Timeout 9 25
Sql Query 6 68
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

821 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