Update tables of another database.

Hi Experts,

I have one (1) Table1 in MS SQL 2005 database (DB1) with (user:- xx, pw:-xy) that accumulate data every minute

Table1:
xID auto-identity
xTime timestamp
xChannelID - integer
xValue numeric

Then I have another database (DB2) in the same SQL 2005 Server with (user:- yy, pw:-yz)
With the following table:

TableA
yChannelID
yHourlyValue - sum of Table1 hourly reading including seconds (xtime) * Table1.xValue
yRate double
yDay equivalent Day
yConsumed = yHourlyValue*yRate

TableB
yChannelID
yDailyValue - sum of Table1 24-hour reading including seconds (xtime) * Table1.xValue or Sum(vHourlyValue) in vDay filter.
yRate - double
yConsumed = yDailyValue*yRate

How can I update TableA & TableB from Table1 using Transactions, or CLR / stored procedure?

Thanks,

Solution: Update tables of another database.

>xTime timestamp
does not seem right. please use DATETIME instead, as timestamp is NOT real time in ms sql server.
http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

now, you can obviously UPDATE or INSERT from one table into another.
the question is, what rule(s) do you want to apply.

here the "generic" code for insert/update

                       
         
           
           
             INSERT INTO db2.dbo.table2 (col1, col2...)
  SELECT col1, col2 ...
   FROM db1.dbo.table1
   WHERE ...
   GROUP BY ...
 ... etc ...
 
UPDATE t2
   SET col2 = t1.col2
 FROM db2.dbo.table2 t2
 JOIN db1.dbo.table1 t1
   ON t1.col1 = t2.col1
  ...

           
         

Open in New Window Select All

Popular Tags

Browse All Tags