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
data
CREATE TABLE Temp_IDs  
            (  
            id INT, 
            UploadDate      DateTime, 
            ToolType varchar(2), 
            SerialNumber INT NOT NULL, 
            LocationID INT  
            ); 

Open in new window

LVL 6
r3nderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
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

0
Lee SavidgeCommented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

r3nderAuthor Commented:
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
0
r3nderAuthor Commented:
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

0
Brian CroweDatabase AdministratorCommented:
What you're essentially looking for is a de-pivot of the data you are currently pulling:

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
r3nderAuthor Commented:
sorry out of town, will check monday
0
r3nderAuthor Commented:
Thanks Brian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.