asked on

Insert into table

I want to take this query and insert it into this table I created

SELECT TOP 1 Max(ta.uploadtime) AS date, 
             j.jobid, 
             jde.toolnumber as 'TN', 
             
             ta.controllersn as 'CO', 
             ta.batterygaugesn as 'BG', 
             ta.magneticssn as 'SP',
            j.DefaultLocationIndex
FROM   Job j 
       LEFT JOIN JobDownholeEquipment jde 
              ON j.jobid = jde.jobid 
       LEFT JOIN ToolAssemblies ta 
              ON jde.toolnumber = ta.toolnumber 
WHERE  ( j.jobinprogress = 'true' 
          OR j.jobinitiated = 'true' ) 
       AND jde.pickedup = 0 
       AND j.defaultlocationindex = 4 
       AND ta.toolnumber = jde.toolnumber 
GROUP  BY j.jobid, 
          jde.toolnumber, 
          ta.uploadtime, 
          ta.controllersn, 
          ta.batterygaugesn, 
          ta.magneticssn 
ORDER  BY ta.uploadtime DESC

Open in new window

the uploaddate whould be uploaddate for each entry
The tooltype would be the column name, serialnumber is the individual serialnumber the locationID is the defaultlocationIndex
each serial number is 1 entry

CREATE TABLE Temp_IDs  
            (  
            id INT, 
            UploadDate      DateTime, 
            ToolType varchar(2), 
            SerialNumber INT NOT NULL, 
            LocationID INT  
            );

Open in new window

Lee

If the select query gives you the exact data in the right format thenjust wrap an insert into around it:

insert into Temp_IDs

SELECT TOP 1 Max(ta.uploadtime) AS date, 
             j.jobid, 
             jde.toolnumber as 'TN', 
             
             ta.controllersn as 'CO', 
             ta.batterygaugesn as 'BG', 
             ta.magneticssn as 'SP',
            j.DefaultLocationIndex
FROM   Job j 
       LEFT JOIN JobDownholeEquipment jde 
              ON j.jobid = jde.jobid 
       LEFT JOIN ToolAssemblies ta 
              ON jde.toolnumber = ta.toolnumber 
WHERE  ( j.jobinprogress = 'true' 
          OR j.jobinitiated = 'true' ) 
       AND jde.pickedup = 0 
       AND j.defaultlocationindex = 4 
       AND ta.toolnumber = jde.toolnumber 
GROUP  BY j.jobid, 
          jde.toolnumber, 
          ta.uploadtime, 
          ta.controllersn, 
          ta.batterygaugesn, 
          ta.magneticssn 
ORDER  BY ta.uploadtime DESC

Open in new window

Lee

See here:

http://www.w3schools.com/sql/sql_insert_into_select.asp

Vitor Montalvão

Something like this?

INSERT INTO Temp_IDs  
            (  
            id INT, 
            UploadDate, 
            ToolType varchar(2), 
            SerialNumber INT NOT NULL, 
            LocationID INT  
            )
SELECT TOP 1 j.jobid, 
		ta.uploadtime,  
            jde.toolnumber, 
             'TN',
             1, 
            j.DefaultLocationIndex
FROM   Job j 
       LEFT JOIN JobDownholeEquipment jde 
              ON j.jobid = jde.jobid 
       LEFT JOIN ToolAssemblies ta 
              ON jde.toolnumber = ta.toolnumber 
WHERE  ( j.jobinprogress = 'true' 
          OR j.jobinitiated = 'true' ) 
       AND jde.pickedup = 0 
       AND j.defaultlocationindex = 4 
       AND ta.toolnumber = jde.toolnumber 
ORDER  BY ta.uploadtime DESC

Open in new window

r3nder

ASKER

Data inserted
1 | 2015-03-09 15:59:19:000 | TN | 52 | 4
2 | 2015-03-09 15:59:19:000 | CO | 1048 | 4
3 | 2015-03-09 15:59:19:000 | BG | 2079 | 4
--skip SP if it is 0

r3nder

ASKER

updated query

SELECT TOP 1 Max(ta.uploadtime) AS date, 
             j.jobid, 
             jde.toolnumber as 'TN', 
             
             ta.controllersn as 'CO', 
             ta.batterygaugesn as 'BG', 
             ta.magneticssn as 'SP',
             j.DefaultLocationIndex
FROM   Job j 
       LEFT JOIN JobDownholeEquipment jde 
              ON j.jobid = jde.jobid 
       LEFT JOIN ToolAssemblies ta 
              ON jde.toolnumber = ta.toolnumber 
WHERE  ( j.jobinprogress = 'true' 
          OR j.jobinitiated = 'true' ) 
       AND jde.pickedup = 0 
       AND j.defaultlocationindex = 4 
       AND ta.toolnumber = jde.toolnumber 
GROUP  BY j.jobid, 
          jde.toolnumber, 
          ta.uploadtime, 
          ta.controllersn, 
          ta.batterygaugesn, 
          ta.magneticssn,
          j.DefaultLocationIndex 
ORDER  BY ta.uploadtime DESC

Open in new window

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

You can use Common Table Expression (CTE):

WITH MyCTE (UploadDate, JobID, TN, CO, BG, SP, LocationID)
AS (SELECT TOP 1 Max(ta.uploadtime), 
		j.jobid, 
        jde.toolnumber, 
        ta.controllersn, 
        ta.batterygaugesn, 
        ta.magneticssn,
        j.DefaultLocationIndex
	FROM Job j 
		LEFT JOIN JobDownholeEquipment jde 
			ON j.jobid = jde.jobid 
		LEFT JOIN ToolAssemblies ta 
			ON jde.toolnumber = ta.toolnumber 
	WHERE  ( j.jobinprogress = 'true' 
          OR j.jobinitiated = 'true' ) 
       AND jde.pickedup = 0 
       AND j.defaultlocationindex = 4 
       AND ta.toolnumber = jde.toolnumber 
	GROUP  BY j.jobid, 
          jde.toolnumber, 
          ta.uploadtime, 
          ta.controllersn, 
          ta.batterygaugesn, 
          ta.magneticssn,
          j.DefaultLocationIndex 
	ORDER  BY ta.uploadtime DESC)
 
INSERT INTO Temp_IDs (id, UploadDate, ToolType, SerialNumber, LocationID)
SELECT JobID, UploadDate, 'TN', TN, LocationID
FROM MyCTE
UNION ALL
SELECT JobID, UploadDate, 'CO', CO, LocationID
FROM MyCTE
SELECT JobID, UploadDate, 'SP', SP, LocationID
FROM MyCTE

Open in new window

r3nder

ASKER

sorry out of town, will check monday

r3nder

ASKER

Thanks Brian