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

• tmmutsetse

  Hall of Fame

  Points: 3669

  More actions

  June 13, 2016 at 6:44 am

  #1884622

  I want to use Select INTO

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  June 13, 2016 at 6:47 am

  #1884624

  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

  June 13, 2016 at 6:48 am

  #1884625

  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

  June 13, 2016 at 6:55 am

  #1884627

  Thanks it worked fine

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 13, 2016 at 7:01 am

  #1884630

  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

  June 13, 2016 at 7:13 am

  #1884635

  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