Results to new table

• Ebays7

  SSC Enthusiast

  Points: 198

  More actions

  December 15, 2016 at 8:10 am

  #332820

  How can I insert into a new table (Table 3) The example below:

  Table 1 -

  Column1

  Texas

  Oklahoma

  Table 2 -

  Column1

  1

  2

  Table 3:

  Column1 Column2

  Texas 1

  Texas 2

  Oklahoma 1

  Oklahoma 2

  Thanks,

  Baze

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 15, 2016 at 8:27 am

  #1918202

  Use INSERT INTO...SELECT...

  And use a cross join to get the expected result.

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 15, 2016 at 8:27 am

  #1918203

  SELECT *

  INTO Table3

  FROM Table1 CROSS JOIN Table2

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 15, 2016 at 8:27 am

  #1918204

  what you are looking for is a Cartesian Product;

  also known as a cross join

  the syntax is pretty simple:

  SELECT * FROM #Table1

  CROSS JOIN #Table2

  in the future, if you want the best help, put code in place that actually creates and populates your sample tables; then anyone can copy paste your sample,and come up with a tested , working solution for whatever problem you present.

  here's a full example:

  IF OBJECT_ID('tempdb.[dbo].[#Table1]') IS NOT NULL

  DROP TABLE [dbo].[#Table1]

  GO

  CREATE TABLE #Table1(Column1 VARCHAR(30) )

  INSERT INTO #Table1

  VALUES('Texas'),('Oklahoma')

  IF OBJECT_ID('tempdb.[dbo].[#Table2]') IS NOT NULL

  DROP TABLE [dbo].[#Table2]

  GO

  CREATE TABLE #Table2(Column1 int )

  INSERT INTO #Table2

  VALUES(1),(2)

  SELECT * FROM #Table1

  CROSS JOIN #Table2

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  December 15, 2016 at 8:30 am

  #1918206

  cboese (12/15/2016)

  ---

  How can I insert into a new table (Table 3) The example below:

  Table 1 -

  Column1

  Texas

  Oklahoma

  Table 2 -

  Column1

  1

  2

  Table 3:

  Column1 Column2

  Texas 1

  Texas 2

  Oklahoma 1

  Oklahoma 2

  Thanks,

  Baze

  If Table3 doesn't already exist, use SELECT/INTO with a CROSS JOIN.

  SELECT Column1 = t1.Column1

  ,Column2 = t2.Column1

  INTO dbo.Table2

  FROM dbo.Table1 t1

  CROSS JOIN dbo.Table2 t2

  ;

  If Table3 already exists, then change the query into an INSERT/SELECT with the same CROSS JOIN.

  INSERT INTO dbo.Table3

  (Column1, Column2)

  SELECT Column1 = t1.Column1

  ,Column2 = t2.Column1

  FROM dbo.Table1 t1

  CROSS JOIN dbo.Table2 t2

  ;

  {EDIT} Went from 0 replies to all those in the time it took me to type my answer.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 15, 2016 at 8:58 am

  #1918217

  Jeff Moden (12/15/2016)

  ---

  {EDIT} Went from 0 replies to all those in the time it took me to type my answer.

  Gail, Lowell and I posted almost at the same time. I was going to edit my answer, but they had already given better answers.

  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