INSERT INTO, using a SELECT and UNION ALL

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  December 18, 2009 at 9:12 am

  #99976

  Hello Everyone

  I am working thru some samples on this chilly day. Not much else to do on a friday afternoon.

  Sample Code:

  CREATE TABLE #MyHead

  (PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  CsvColumn VARCHAR(500))

  INSERT INTO #MyHead

  SELECT '1,5,3,7,8,2' UNION ALL

  SELECT '7,2,3,7,1,2,2' UNION ALL

  SELECT '4,7,5' UNION ALL

  SELECT '1' UNION ALL

  SELECT '5' UNION ALL

  SELECT '2,6' UNION ALL

  SELECT '1,2,3,4,55,6'

  SELECT * FROM #MyHead

  I am trying to better understand the use of the UNION ALL, in the SELECT statement. I know what it does to the temp table in the example. If the UNION ALL is not present, and the query is ran, the table contains only one row. If the UNION ALL Statement is present, there are 7 rows Inserted into the table. I cannot seem to find anything in the SQL BOL that describe this.

  I am hoping that someone can clarify this, or point me in the direction of some good reading.

  Thanks

  Andrew

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  December 19, 2009 at 12:22 am

  #1094417

  Sorry if I am underestimating your requirement, but 'UNION' will combine the result sets and all your 7 select query will act as one single select query. This combined result will be inserted into the temp table.

  If you remove UNION, the immediate select following the insert will be used to insert the data into the temp table. Rest all will be unioned and displayed.

  Although not exactly what are you for, Example B in the link provided should explain you what happens.

  http://technet.microsoft.com/en-us/library/ms180026(SQL.90).aspx

  ---------------------------------------------------------------------------------

• gints.plivna

  Ten Centuries

  Points: 1256

  More actions

  December 19, 2009 at 10:00 am

  #1094460

  For more info about SQL set operators read this.

  Gints Plivna
  http://www.gplivna.eu