Query Help

• easy_goer

  SSC Enthusiast

  Points: 166

  More actions

  September 30, 2013 at 9:35 am

  #283785

  Hello.

  I have one table that looks something like this..

  3022224 G 4980 65 3

  3022224 U 4980 596 2

  3022224 G 4980 67 3

  And another that looks something like this..

  28077 1 3022224

  28078 1 3022224

  28079 1 3022224

  I need insert the following into another table based on the two tables above. The long number (3022224) is what I use to join the two original tables.

  28077 1 G 4980 65 3

  28077 1 U 4980 596 2

  28077 1 G 4980 67 3

  28078 1 G 4980 65 3

  28078 1 U 4980 596 2

  28078 1 G 4980 67 3

  28079 1 G 4980 65 3

  28079 1 U 4980 596 2

  28079 1 G 4980 67 3

  Any ideas would be much appreciated.

  Thank you,

  Keith

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  September 30, 2013 at 9:38 am

  #1654616

  Why would you bother, all the data is in the two tables, which you can select anytime. Why would you want to store a bunch of duplicate data. That means that every insert into either of the original tables, would then also mean an insert into the third table. You could simply create a query or a View if you want that data.

  Andrew SQLDBA

  Hello.

  I have one table that looks something like this..

  3022224 G 4980 65 3

  3022224 U 4980 596 2

  3022224 G 4980 67 3

  And another that looks something like this..

  28077 1 3022224

  28078 1 3022224

  28079 1 3022224

  I need insert the following into another table based on the two tables above. The long number (3022224) is what I use to join the two original tables.

  28077 1 G 4980 65 3

  28077 1 U 4980 596 2

  28077 1 G 4980 67 3

  28078 1 G 4980 65 3

  28078 1 U 4980 596 2

  28078 1 G 4980 67 3

  28079 1 G 4980 65 3

  28079 1 U 4980 596 2

  28079 1 G 4980 67 3

  Any ideas would be much appreciated.

  Thank you,

  Keith

• homebrew01

  SSC Guru

  Points: 55539

  More actions

  September 30, 2013 at 9:51 am

  #1654623

  Also, "query help" suggests that you have worked on it, but are stuck.

  What have you got so far ?

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 30, 2013 at 10:09 am

  #1654631

  This will get you what you are looking for...

  -- (1) Sample data

  DECLARE @x_p TABLE (xid int, nbr1 int, cid int);

  DECLARE @x_c TABLE (cid int, chr char(1), nbr2 int, nbr3 int, nbr4 int);

  INSERT @x_p VALUES (28077,1,3022224),(28078,1,3022224),(28079,1,3022224);

  INSERT @x_c VALUES (3022224,'G',4980,65,3),(3022224,'U',4980,596,2),(3022224,'G',4980,67,3);

  --(2) The query

  SELECT xid, nbr1, chr, nbr2, nbr3, nbr4

  FROM @x_p xp

  CROSS APPLY @x_c xc

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• easy_goer

  SSC Enthusiast

  Points: 166

  More actions

  September 30, 2013 at 10:43 am

  #1654649

  Think I got it. Thanks for all of the help!