SQL Server Performance

debugging help needed urgently

Discussion in 'General Developer Questions' started by gaurangpadhye, Oct 12, 2006.

  1. gaurangpadhye New Member

    HI, i am trying to insert values from columns into variables by using the SELECT INTO Statement but i think i am making some syntax error. So can someone please debug it fopr me

    Here's the code


    select a.Dvsn_Ind, a.MS_Plat_ID, a.Muni_ID,
    b.Asst_ID, b.Asst_Nmbr, b.Asst_Name, b.Locn_Cmnt,
    d.Asst_Type_ID, d.Asst_Styp_ID

    into dvsn_ind, ms_plat_id, muni_id, asst_id, asst_nmbr, asst_name, locn_desc, asst_type_id, asst_styp_id
    from ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_LOCATION_HIERARCHY a,
    ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET b,
    ESVL_MASTERLOG c,
    ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET_HIERARCHY d
    WHERE a.Locn_Hrch_ID = b.Locn_Hrch_ID
    AND b.Asst_ID = c.Asst_ID
    AND b.Asst_Hrch_ID = d.Asst_Hrch_ID

    The error which i get is:

    Server: Msg 170, Level 15, State 1, Line 17
    Line 17: Incorrect syntax near ','.


    Pl can someone help.
    thanks
    Gaurang
  2. Adriaan New Member

    The syntax for an existing target table is:<br /><br />INSERT INTO &lt;target_table&gt; (&lt;column_list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT ......................<br />FROM ................<br /><br />The syntax you were trying can only be used to create a new table, without defining the table columns:<br /><br />SELECT .........<br />INTO &lt;new_table&gt;<br />FROM ...........<br /><br />For that purpose, it is preferred to do a CREATE TABLE and then use the INSERT INTO syntax.
  3. gaurangpadhye New Member

    So is there any way in I can select the values from the columns in my tables and put them into local variables??
    i am using these statement inside a cursor loop
  4. gaurangpadhye New Member

    what do u think adriaan?
  5. Adriaan New Member

    Sorry, I missed the variables bit. Before I go into the variables syntax ...

    Why do you need so many variables?

    What are you planning to with those variables?
  6. gaurangpadhye New Member

    i have to insert the values in these variables into columns of other tables in my database inside the cursor loop i have.
  7. Adriaan New Member

    T-SQL is programming language, but it is data-oriented. You can directly copy data from columns in one table into columns in another table, without using any variables.

    Also, in 99% of all cases you don't have to loop through a cursor.

    The basic syntax is:

    INSERT INTO TargetTable (ColumnA, ColumnB, ColumnC)
    SELECT ColumnX, ColumnY, ColumnZ
    FROM SourceTable1 t1

    Just replace the fantasy names with your actual ones. You have the full SELECT syntax to your disposal: JOINs, WHERE, GROUP BY, ORDER BY, etc. etc.
  8. gaurangpadhye New Member

    BUT IF I HAVE A CURSOR LOOP ISNT THERE ANYWAY IN WHICH I CAN ASSIGN THE VALUES OF THE SELECTED COLUMNS TO LOCAL VARIABLES WHICH I CAN USE FURTHER IN MY CURSOR LOOP??
  9. Adriaan New Member

    Read the DECLARE CURSOR section in Books Online.
  10. gaurangpadhye New Member

  11. Bredsox New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by gaurangpadhye</i><br /><br />i have to insert the values in these variables into columns of other tables in my database inside the cursor loop i have.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Are you looking to assign the values to the variables?<br /><br /><div align="left">DECLARE @Dvsn_Ind int <br />,@Muni_ID int<br />,@Asst_ID int<br />,@Asst_Nmbr int<br />,@Asst_Type_ID int<br />,@Asst_Styp_ID int<br />,@Asst_Name varchar(55)<br />,@Locn_Cmnt varchar(100)</div id="left"><br /><div align="left">SELECT@Dvsn_Ind = a.Dvsn.Ind <br />,@Muni_ID = a.Muni_ID<br />,@Asst_ID = b.Asst_ID<br />,@Asst_Nmbr = b.Asst_Nmbr<br />,@Asst_Name = b.Asst_Name<br />,@Locn_Cmnt = b.Locn_Cmnt<br />,@Asst_Type_ID = d.Asst_Type_ID<br />,@Asst_Styp_ID = d.Asst_Styp_ID</div id="left"><br /><div align="left">FROM ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_LOCATION_HIERARCHY a</div id="left"> <br /><div align="left">JOINELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET b</div id="left"> <br /><div align="left">ONa.Locn_Hrch_ID = b.Locn_Hrch_ID</div id="left"> <br /><div align="left">JOINESVL_MASTERLOG c, ,</div id="left"> <br /><div align="left">ONb.Asst_ID = c.Asst_ID</div id="left"> <br /><div align="left">JOINELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET_HIERARCHY d</div id="left"> <br /><div align="left">ONb.Asst_Hrch_ID = d.Asst_Hrch_ID</div id="left"> <br /><div align="left">WHERE SOME_CONDITION --which returns one record each</div id="left"> <br /><br />As Adriaan pointed out, why do you want to use cursor just to insert values from one table to another table? Also, why do you need to join table ESVL_MASTERLOG c if you are not selecting anything from this?<br /><br />BTW, I am trying to learn how to format the code based on forum code to make it pretty that's why I have a lot of edits <img src='/community/emoticons/emotion-5.gif' alt=';)' />
  12. gaurangpadhye New Member

  13. Adriaan New Member

    About the formatting: do the indentations in Windows Notepad, then copy-and-paste into the forum.
  14. Bredsox New Member

    quote:Originally posted by Adriaan

    About the formatting: do the indentations in Windows Notepad, then copy-and-paste into the forum.

    Adriaan,
    Let me try now as you suggested(copying format from notepad). I know I have asked this before and Frank suggested me to include []something[/] whethere left/right/center but never got it working.


    DECLARE @Dvsn_Ind int
    ,@Muni_ID int
    ,@Asst_ID int
    ,@Asst_Nmbr int
    ,@Asst_Type_ID int
    ,@Asst_Styp_ID int
    ,@Asst_Name varchar(55)
    ,@Locn_Cmnt varchar(100)
    SELECT@Dvsn_Ind = a.Dvsn.Ind
    ,@Muni_ID = a.Muni_ID
    ,@Asst_ID = b.Asst_ID
    ,@Asst_Nmbr = b.Asst_Nmbr
    ,@Asst_Name = b.Asst_Name
    ,@Locn_Cmnt = b.Locn_Cmnt
    ,@Asst_Type_ID = d.Asst_Type_ID
    ,@Asst_Styp_ID = d.Asst_Styp_ID
    FROM ELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_LOCATION_HIERARCHY a
    JOINELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET b
    ONa.Locn_Hrch_ID = b.Locn_Hrch_ID
    JOINESVL_MASTERLOG c,
    ONb.Asst_ID = c.Asst_ID
    JOINELECTDISTINSPECTIONS_ASSETDW.ASSETDW.dbo.ADW_ASSET_HIERARCHY d
    ONb.Asst_Hrch_ID = d.Asst_Hrch_ID
    WHERE SOME_CONDITION --which returns one record each
    GO
  15. Bredsox New Member

    Didn't work. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />I gave up formatting. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />
  16. Bredsox New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Finally got it after editing the previous post.

Share This Page