Creating #Table

Question

Post reply

Creating #Table

tmmutsetse

Hall of Fame

Points: 3669
More actions
June 13, 2016 at 6:28 am

#400268

Good day Experts,
How can i insert the results of the following query on a #Table using "INTO #Table"
SELECT 'New Dmark' as sitename,*
FROM
(SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%New Denmark%'
INNER JOIN sem5.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN sem5.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM sem5.dbo.SEM_COMPUTER
INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [sem5].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%New Dmark%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION) as a
Union all
SELECT 'New Dmark' as sitename,*
FROM
(SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
PATTERN.Version AS 'Virus definition used',
dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
FROM SEM12Anglo.dbo.SEM_COMPUTER
INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [SEM12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%New Demark%'
INNER JOIN SEM12Anglo.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
INNER JOIN SEM12Anglo.dbo.PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
INNER JOIN (
SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
FROM SEM12Anglo.dbo.SEM_COMPUTER
INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
inner join [SEM12Anglo].dbo.[IDENTITY_MAP] as d
on d.id = SEM_AGENT.group_id and d.Name like '%New Demark%'
GROUP BY COMPUTER_NAME)
TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
AND PATTERN.DELETED='0'
AND SEM_CONTENT.DELETED='0'
AND SEM_AGENT.DELETED='0'
AND SEM_COMPUTER.DELETED='0'
GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
) as b

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 1

tmmutsetse

Hall of Fame

Points: 3669

June 13, 2016 at 6:44 am

#1884622

I want to use Select INTO

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 2

SELECT *

INTO #Table

FROM ( SELECT 'New Dmark' AS sitename

,*

FROM ( SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name'

,PATTERN.Version AS 'Virus definition used'

,DATEADD(SECOND,

SEM_AGENT.LAST_UPDATE_TIME / 1000,

'1970-01-01') AS 'Last check-in (GMT)'

,SEM_AGENT.AGENT_VERSION

FROM sem5.dbo.SEM_COMPUTER

INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [sem5].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.Name LIKE '%New Denmark%'

INNER JOIN sem5.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID = SEM_AGENT.AGENT_ID

INNER JOIN sem5.dbo.PATTERN ON PATTERN.PATTERN_IDX = SEM_AGENT.PATTERN_IDX

INNER JOIN ( SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName'

,MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'

FROM sem5.dbo.SEM_COMPUTER

INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [sem5].dbo.[IDENTITY_MAP]

AS d ON d.id = SEM_AGENT.group_id

AND d.Name LIKE '%New Dmark%'

GROUP BY COMPUTER_NAME

) TestTable ON TestTable.TempHostName = SEM_COMPUTER.COMPUTER_NAME

AND TestTable.TempMax = SEM_AGENT.LAST_UPDATE_TIME

WHERE PATTERN.PATTERN_TYPE = 'VIRUS_DEFS'

AND PATTERN.DELETED = '0'

AND SEM_CONTENT.DELETED = '0'

AND SEM_AGENT.DELETED = '0'

AND SEM_COMPUTER.DELETED = '0'

GROUP BY SEM_COMPUTER.COMPUTER_NAME

,SEM_AGENT.LAST_UPDATE_TIME

,PATTERN.Version

,SEM_AGENT.AGENT_VERSION

) AS a

UNION ALL

SELECT 'New Dmark' AS sitename

,*

FROM ( SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name'

,PATTERN.Version AS 'Virus definition used'

,DATEADD(SECOND,

SEM_AGENT.LAST_UPDATE_TIME / 1000,

'1970-01-01') AS 'Last check-in (GMT)'

,SEM_AGENT.AGENT_VERSION

FROM SEM12Anglo.dbo.SEM_COMPUTER

INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [SEM12Anglo].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.Name LIKE '%New Demark%'

INNER JOIN SEM12Anglo.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID = SEM_AGENT.AGENT_ID

INNER JOIN SEM12Anglo.dbo.PATTERN ON PATTERN.PATTERN_IDX = SEM_AGENT.PATTERN_IDX

INNER JOIN ( SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName'

,MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'

FROM SEM12Anglo.dbo.SEM_COMPUTER

INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [SEM12Anglo].dbo.[IDENTITY_MAP]

AS d ON d.id = SEM_AGENT.group_id

AND d.Name LIKE '%New Demark%'

GROUP BY COMPUTER_NAME

) TestTable ON TestTable.TempHostName = SEM_COMPUTER.COMPUTER_NAME

AND TestTable.TempMax = SEM_AGENT.LAST_UPDATE_TIME

WHERE PATTERN.PATTERN_TYPE = 'VIRUS_DEFS'

AND PATTERN.DELETED = '0'

AND SEM_CONTENT.DELETED = '0'

AND SEM_AGENT.DELETED = '0'

AND SEM_COMPUTER.DELETED = '0'

GROUP BY SEM_COMPUTER.COMPUTER_NAME

,SEM_AGENT.LAST_UPDATE_TIME

,PATTERN.Version

,SEM_AGENT.AGENT_VERSION

) AS b

) AS z;

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

Quick & dirty way:

SELECT yourqueryresults.*

INTO #temptablename

FROM

( <<your query here>> ) yourqueryresults;

Thomas Rushton
blog: https://thelonedba.wordpress.com

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 4

tmmutsetse

Hall of Fame

Points: 3669

June 13, 2016 at 6:55 am

#1884627

Thanks it worked fine

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The two queries use different filters on the IDENTITY_MAP table. The first query (database sem5) uses

d.Name like '%New Denmark%' in the FROM list and

d.Name like '%New Dmark%' in the derived table [TestTable]. The second query (database SEM12Anglo) uses

d.Name like '%New Demark%' in both locations in the query. Is this deliberate or an oversight? There is a reason for asking.

You can run the results into a #temp table in several ways. Easiest is probably to wrap the lot up as a (derived table) or CTE and SELECT …INTO from there.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

It seems like you're looking for the syntax from this example.

CREATE TABLE TableA( id int identity, value varchar(100), somedate datetime DEFAULT GETDATE());

INSERT INTO TableA(value) SELECT 'One Value';

CREATE TABLE TableB( id int identity, value varchar(100), somedate datetime DEFAULT GETDATE());

INSERT INTO TableB(value) SELECT 'Other Value';

SELECT *

INTO #Test

FROM TableA

UNION ALL

SELECT *

FROM TableB;

SELECT * FROM #Test;

GO

DROP TABLE TableA;

DROP TABLE TableB;

DROP TABLE #Test

Here's an example using your code

SELECT 'New Dmark' AS sitename

,SEM_COMPUTER.COMPUTER_NAME AS 'Computer name'

,PATTERN.Version AS 'Virus definition used'

,dateadd(second, SEM_AGENT.LAST_UPDATE_TIME / 1000, '1970-01-01') AS 'Last check-in (GMT)'

,SEM_AGENT.AGENT_VERSION

INTO #Table -- INTO clause is used in the first select between the column list and the FROM clause.

FROM sem5.dbo.SEM_COMPUTER

INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [sem5].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.NAME LIKE '%New Denmark%'

INNER JOIN sem5.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID = SEM_AGENT.AGENT_ID

INNER JOIN sem5.dbo.PATTERN ON PATTERN.PATTERN_IDX = SEM_AGENT.PATTERN_IDX

INNER JOIN (

SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName'

,MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'

FROM sem5.dbo.SEM_COMPUTER

INNER JOIN sem5.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [sem5].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.NAME LIKE '%New Dmark%'

GROUP BY COMPUTER_NAME

) TestTable ON TestTable.TempHostName = SEM_COMPUTER.COMPUTER_NAME

AND TestTable.TempMax = SEM_AGENT.LAST_UPDATE_TIME

WHERE PATTERN.PATTERN_TYPE = 'VIRUS_DEFS'

AND PATTERN.DELETED = '0'

AND SEM_CONTENT.DELETED = '0'

AND SEM_AGENT.DELETED = '0'

AND SEM_COMPUTER.DELETED = '0'

GROUP BY SEM_COMPUTER.COMPUTER_NAME

,SEM_AGENT.LAST_UPDATE_TIME

,PATTERN.Version

,SEM_AGENT.AGENT_VERSION

UNION ALL

SELECT 'New Dmark' AS sitename

,SEM_COMPUTER.COMPUTER_NAME AS 'Computer name'

,PATTERN.Version AS 'Virus definition used'

,dateadd(second, SEM_AGENT.LAST_UPDATE_TIME / 1000, '1970-01-01') AS 'Last check-in (GMT)'

,SEM_AGENT.AGENT_VERSION

FROM SEM12Anglo.dbo.SEM_COMPUTER

INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [SEM12Anglo].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.NAME LIKE '%New Demark%'

INNER JOIN SEM12Anglo.dbo.SEM_CONTENT ON SEM_CONTENT.AGENT_ID = SEM_AGENT.AGENT_ID

INNER JOIN SEM12Anglo.dbo.PATTERN ON PATTERN.PATTERN_IDX = SEM_AGENT.PATTERN_IDX

INNER JOIN (

SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName'

,MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'

FROM SEM12Anglo.dbo.SEM_COMPUTER

INNER JOIN SEM12Anglo.dbo.SEM_AGENT ON SEM_AGENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID

INNER JOIN [SEM12Anglo].dbo.[IDENTITY_MAP] AS d ON d.id = SEM_AGENT.group_id

AND d.NAME LIKE '%New Demark%'

GROUP BY COMPUTER_NAME

) TestTable ON TestTable.TempHostName = SEM_COMPUTER.COMPUTER_NAME

AND TestTable.TempMax = SEM_AGENT.LAST_UPDATE_TIME

WHERE PATTERN.PATTERN_TYPE = 'VIRUS_DEFS'

AND PATTERN.DELETED = '0'

AND SEM_CONTENT.DELETED = '0'

AND SEM_AGENT.DELETED = '0'

AND SEM_COMPUTER.DELETED = '0'

GROUP BY SEM_COMPUTER.COMPUTER_NAME

,SEM_AGENT.LAST_UPDATE_TIME

,PATTERN.Version

,SEM_AGENT.AGENT_VERSION;

The syntax becomes more evident when understanding that UNION ALL is the same kind of operator than EXCEPT. The first set/query is the main one and therefor is the one that should include the INTO clause.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2