Table Variables How and When???

• cedar72

  Say Hey Kid

  Points: 711

  More actions

  July 16, 2004 at 11:26 am

  #108503

  I am trying to learn how to use a table variable instead of a temp table, could some one explain how to do the following using a table variable instead of a temp table?

  SELECT jsa.JobStartID, jsa.ActionTypeId, aty.ActionName, jsa.ActionDt AS QC_Approved

  Into tmpQCApproved

  FROM homelink..JobstartAction jsa LEFT JOIN homelink..ActionType aty ON jsa.ActionTypeId = aty.ActionTypeId

  WHERE jsa.ActionTypeId=5;

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 16, 2004 at 12:22 pm

  #514928

  hey cedar, try this link:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

  for your your case, you would first need to declare a table variable say like this:

  DECLARE @tempTable TABLE (

    JobStartID <datatype goes here>,

    ActionTypeId <datatype goes here>,

    ActionName <datatype goes here>,

    QC_Approved <datatype goes here>

  )

  then, to populate the table variable you would do something like this:

  INSERT INTO @tempTable(JobStartID, ActionTypeId, ActionName, QC_Approved)

  SELECT

    jsa.JobStartID, jsa.ActionTypeId, aty.ActionName, jsa.ActionDt AS QC_Approved

  FROM

    homelink..JobstartAction jsa LEFT JOIN homelink..ActionType aty ON jsa.ActionTypeId = aty.ActionTypeId

  WHERE

    jsa.ActionTypeId=5

  regards,

  JP

• cedar72

  Say Hey Kid

  Points: 711

  More actions

  July 16, 2004 at 12:34 pm

  #514931

  Thank you, worked perfectly!