MERGING TABLES - SQL QUERY .. HELP PLS

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  July 22, 2010 at 1:12 pm

  #225251

  Hi,

  Can any one help me on this..

  Here I have 2 tables, call it as parent table, child table.. I want to merge these two tables and need to export to the text file....

  CREATE TABLE #TEMP_MAIN (SNO INT ,ID INT ,PRODUCT VARCHAR(100),LOC VARCHAR(100))

  CREATE TABLE #TEMP_CHILD (SNO INT ,ID INT ,SUB_PRODUCT VARCHAR(100),PRICE NUMERIC(16,3))

  INSERT INTO #TEMP_MAIN

  SELECT 1,1000,'BENZ','USA'

  UNION ALL

  SELECT 1,1001,'ASHOK LAYLAND','IND'

  INSERT INTO #TEMP_CHILD

  SELECT 2,1000,'A-CLASS',10000000

  UNION ALL

  SELECT 2,1000,'B-CLASS',20000000

  UNION ALL

  SELECT 2,1000,'C-CLASS',30000000

  UNION ALL

  SELECT 2,1001,'TRUCK',2500000

  UNION ALL

  SELECT 2,1001,'BUS',3500000

  Now I want to export these table results to text file.

  Data should be like this..

  11000BENZUSA

  21000A-CLASS10000000.000

  21000B-CLASS20000000.000

  21000C-CLASS30000000.000

  11001ASHOK LAYLANDIND

  21001TRUCK2500000.000

  21001BUS3500000.000

  Merging should be based on id column..

  Please help on this

  🙂

• Mike01

  SSChampion

  Points: 11420

  More actions

  July 22, 2010 at 1:25 pm

  #1197138

  Not sure what you want to call the last column but here goes. I think you were looking for a header record then the following detail records.

  CREATE TABLE #TEMP_MAIN (SNO INT ,ID INT ,PRODUCT VARCHAR(100),LOC VARCHAR(100))

  CREATE TABLE #TEMP_CHILD (SNO INT ,ID INT ,SUB_PRODUCT VARCHAR(100),PRICE NUMERIC(16,3))

  INSERT INTO #TEMP_MAIN

  SELECT 1,1000,'BENZ','USA'

  UNION ALL

  SELECT 1,1001,'ASHOK LAYLAND','IND'

  INSERT INTO #TEMP_CHILD

  SELECT 2,1000,'A-CLASS',10000000

  UNION ALL

  SELECT 2,1000,'B-CLASS',20000000

  UNION ALL

  SELECT 2,1000,'C-CLASS',30000000

  UNION ALL

  SELECT 2,1001,'TRUCK',2500000

  UNION ALL

  SELECT 2,1001,'BUS',3500000

  select SNO, ID, PRODUCT, LOC from (

  select SNO, ID, PRODUCT, LOC, 1 as OrderNum

  from #Temp_Main

  union all

  select c.SNO, c.ID, c.SUB_PRODUCT, cast(c.PRICE as varchar(20)), 2 as OrderNum

  from #TEMP_MAIN M

  inner join #TEMP_CHILD c

  on M.ID = c.ID) v

  order by ID, OrderNum

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  July 22, 2010 at 2:45 pm

  #1197199

  Yes, Obsolutely

  Thanks mike

  🙂