MS SQL update/insert query

Posted on 2009-04-05
Medium Priority
Last Modified: 2012-05-06
Hello experts, Im newbie to MS SQL. I have two tables both hold the same fields Time, computer name, MAC, IP and Status which can be either logon or logoff. Table1 is populated by a logon script and Table2 should read and maintain from table1 only the first and last time a user logged on\off to any workstation.  
 I have written the following query which update only the last time a user logged on and off. I would like to refine it so it would also keep the first time a user logged on/off and insert a new record if username and computer name is not matching.  I set max pts as this is needed asap. Thanks

   SET username = Table1.username
     , computer = Table1.computer
	 , status = Table1.status
	 , IP = Table1.IP
	 , MAC = Table1.MAC
	 , Time01 = Table1.time01
 FROM Logonoff2
 JOIN Table1
   ON Table2.username = logonoff_all.username 
	and Table12.computer = Table1.computer 
	and Table1.status = Table2.status
	and Table1.time01 > Table2.time01 
INSERT INTO Table2 (Username, Computer, Status, IP, time01)
  SELECT Table1.username, Table1.computer, Table1.status
	, Table1.IP, Table1.time01
  FROM Table1
WHERE         Table2.userName = Table2.username 
	and computer.Table2 = Table1.computer)

Open in new window

Question by:HAKSA
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
  • 2
LVL 43

Expert Comment

ID: 24070946
It should be much easier to have two Time columns in Table2:
SELECT username, computer, IP, MAC, status, MIN(Time01) FirstTime, MAX(Time01) LastTime
  INTO Table2
  FROM Table1
 GROUP BY username, computer, IP, MAC, status

Open in new window

LVL 43

Expert Comment

ID: 24071004
If you need just one pair of time values independently on status, then you may remove status column from above select. If the status is important then you may use following:
SELECT t1.username, t1.computer, t1.IP, t1.MAC, 
       t1.FirstTime, t1min.Status FirstStatus, t1.LastTime, t1max.Status LastStatus
  FROM (SELECT username, computer, IP, MAC, MIN(Time01) FirstTime, MAX(Time01) LastTime
          FROM Table1
         GROUP BY username, computer, IP, MAC) t1
  INNER JOIN Table1 t1min ON t1min.username = t1.username AND t1min.MAC = t1.MAC AND t1min.Time01 = t1.FirstTime
  INNER JOIN Table1 t1max ON t1max.username = t1.username AND t1max.MAC = t1.MAC AND t1max.Time01 = t1.LastTime

Open in new window


Author Comment

ID: 24075636
Hi pcelba,
thanks for your reply. I agree with you having 2 time columns in t2 made it much easier but I still have one small problem. Considering that t1 is a transition table and it will be cleaned up or regular bases,  How can I maintain the min (time) in t2 even after the original record is deleted in t1.
   SET username = T_1.username
     , computer = T_1.computer
	 , status	= T_1.status
	 , IP = T_1.IP
	 , MAC= T_1.MAC
	 , Firsttime= (select min(Time01) from T_1)
	 , Lasttime = (select max(time01) from T_1)
   ON T_2.username = T_1.username 
	and T_1.computer = T_1.computer 
	and T_1.status = T_2.status

Open in new window

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 43

Expert Comment

ID: 24075746
OK, this means you cannot create T_2 by one SELECT command but you have to go back to Insert/Update scenario. Then it should be sufficient to update just max time because the min time cannot change once calculated. It, of course, depends on the way how data are stored in T_1.
LVL 43

Accepted Solution

pcelba earned 2000 total points
ID: 24075922
Let suppose the unique key in T_2 table is username + computer + status (I would prefer username + MAC + status but it does not matter).

You have to group records from T_1 always to avoid duplicities in T_2. And we may suppose both min and max time may change:

SELECT username, computer, IP, MAC, status, MIN(Time01) FirstTime, MAX(Time01) LastTime
  INTO #TempTime
  FROM T_1
 GROUP BY username, computer, IP, MAC, status 
   SET Firsttime = CASE WHEN T_2.FirstTime < #TempTime.FirstTime THEN T_2.FirstTime ELSE #TempTime.FirstTime END,
       Lasttime  = CASE WHEN T_2.LastTime > #TempTime.LastTime THEN T_2.LastTime ELSE #TempTime.LastTime END
 JOIN #TempTime 
   ON T_2.username = #TempTime.username 
	and T_2.computer = #TempTime.computer 
	and T_2.status = #TempTime.status
SELECT username, computer, IP, MAC, status, FirstTime, LastTime
  FROM #TempTime
   (SELECT null FROM T_2 
     WHERE username = #TempTime.username 
       AND computer = #TempTime.computer
       AND status = #TempTime.status)

Open in new window

LVL 43

Expert Comment

ID: 24075941
WHERE NOT EXISTS   of course

Other possible solution exist, as well.

Author Closing Comment

ID: 31566723
It works just like i want it. Thanks

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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