Selecting the dates from two tabl and inserting into a temporay table

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  March 9, 2011 at 7:15 am

  #235913

  I have two tables

  CREATE Table A(

  AID int not null primary key Identity,

  EmployeeID int not null,

  name varchar(30) not null,

  type varchar(10),

  Date1 datetime not null

  )

  GO

  CREATE Table B(

  BID int not null primary key Identity,

  EmployeeID int not null,

  Date2 datetime not null

  )

  GO

  INSERT INTO Table A (EmployeeID ,name ,type ,Date1 )

  SELECT '1','Joe','XXX','2011-03-01'

  UNION ALL

  SELECT '2','James','ZZZ','2011-03-02'

  UNION ALL

  SELECT '3','Raj','QQQ','2011-03-03'

  UNION ALL

  SELECT '4','RO','WWW','2011-03-05'

  GO

  INSERT INTO Table B (EmployeeID ,Date2 )

  SELECT '1','2011-03-01'

  UNION ALL

  SELECT '1','2011-02-26'

  UNION ALL

  SELECT '2','2011-03-08'

  UNION ALL

  SELECT '3','2011-03-06'

  UNION ALL

  SELECT '4','2011-03-05'

  UNION ALL

  SELECT '2','2011-03-04'

  GO

  The date in the two fields are different, I want to insert the Date column along with all other entries into the temporary table #temp .I want all the dates in the temp table

  CREATE TABLE #temp(

  ID int identity(1,1) primary key,

  date datetime,

  EmployeeID int,

  name varchar(30),

  type varchar(30),

  )

  INSERT INTO #temp

  SELECT date, --how to select all the dates from both the table

  employeeID,

  name,

  type

  FROM A

  the date column name in both the table is different

  thank you

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 9, 2011 at 7:18 am

  #1296488

  Not sure if I'm following 100%... Just the dates from Table B? Dates and fetch the rest of the employee data from A? If so will there only be one entry in A per employee?

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 9, 2011 at 7:23 am

  #1296492

  Does this correctly describe the results you want back? If not, what's wrong?

  '1','Joe','XXX','2011-03-01'

  '1','Joe','XXX','2011-03-01'

  '1','Joe','XXX','2011-02-26'

  '2','James','ZZZ','2011-03-02'

  '2','James','ZZZ','2011-03-08'

  '2','James','ZZZ','2011-03-04'

  '3','Raj','QQQ','2011-03-03'

  '3','Raj','QQQ','2011-03-06'

  '4','RO','WWW','2011-03-05'

  '4','RO','WWW','2011-03-05'

  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

• Arjun Sivadasan

  SSCertifiable

  Points: 5902

  More actions

  March 9, 2011 at 8:05 am

  #1296528

  dilipd006 (3/9/2011)

  ---

  I have two tables

  The date in the two fields are different, I want to insert the Date column along with all other entries into the temporary table #temp .I want all the dates in the temp table

  the date column name in both the table is different

  thank you

  How do we make sense out of this dilipd? Please frame your questions better if you expect good help. Thanks for posting the scripts though.

  - arjun

  https://sqlroadie.com/

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 11, 2011 at 1:49 am

  #1297475

  Hi

  You can select both dates as follows:

  INSERT INTO #temp

  SELECT a.date, b.date --how to select all the dates from both the table

  employeeID,

  name,

  type

  FROM A

  join B

  on a.aid=b.bid

  ---OR if you want to combine the columns into 1 column ----

  INSERT INTO #temp

  SELECT

  CASE

  WHEN a.date > b.date THEN a.date ELSE b.date -- put in your own criteria.

  END,

  employeeID,

  name,

  type

  FROM A

  join B

  on a.aid=b.bid

  Is this what you are looking for?...:-)

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 11, 2011 at 1:52 am

  #1297478

  The date in the two fields are different, I want to insert the Date column along with all other entries into the temporary table #temp .I want all the dates in the temp table

  The JOIN should take care of displaying all the dates from B.

  --Not sure how this quote function works, hope this post is readable and makes sense now.:unsure:

• Arjun Sivadasan

  SSCertifiable

  Points: 5902

  More actions

  March 11, 2011 at 2:09 am

  #1297486

  To make text appear as quoted, enclose the text in '

  ' beginning tag and end tag.

  Alternatively, select the text in the editor and click on quote IFCode shorcut. The selected text will appear as quoted text.

  The OP has not responded yet. I hope he was looking for a similar solution.

  - arjun

  https://sqlroadie.com/

• terrykzncs

  Ten Centuries

  Points: 1324

  More actions

  March 14, 2011 at 1:54 pm

  #1298558

  Arjun Sivadasan (3/11/2011)

  ---

  To make text appear as quoted, enclose the text in '

  ' beginning tag and end tag.

  Alternatively, select the text in the editor and click on quote IFCode shorcut. The selected text will appear as quoted text.

  The OP has not responded yet. I hope he was looking for a similar solution.

  - arjun

  Thanks alot!:-D

  I like the first method as it reminds me of html...:rolleyes: