debugging help needed urgently

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

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.

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

what do u think adriaan?

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?

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

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.

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??

Read the DECLARE CURSOR section in Books Online.

thx adriaan

<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='' />

thx a lot

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

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

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 />

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