INSERT INTO a Table FROM another Table

Question

Hi Wat i am doing is to INSERT into a table values from another table that satisfy the L_Linenumber = temp1 value. the temp1 value is a dynamic one that i get from the LineNumberOcurrencesSmal dataset. -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- temp1 = Convert.ToDouble(dsLineNumber.Tables [LineNumberOccurrencesSmall].Rows[Increment].ItemArray[0].ToString()); string fillLineNumberSmallGroupRows = INSERT INTO sunspot. createdTableLineNumberSmallGroupRows(L_OrderKeyL_ PartKeyL_SuppKeyL_LineNumber) SELECT * FROM sunspot.LINEITEM WHERE L_LineNumber = + temp1; conn = new SqlConnection(source); conn.Open(); SqlCommand cmdFillTableLineNumberSmallGroupRows = new SqlCommand (fillLineNumberSmallGroupRows conn); cmdFillTableLineNumberSmallGroupRows.ExecuteNonQuery();<--ERROR -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Is my codes corectly writen? I executed the codes in sql query analyzier and it gave me the results but it give me an error msg when I execute it in visual c#.net 2003.

sunspotzzz · Answer

Hi thanks a lot for your advice tried your codes and it worked! I used a stored procedure. however is there a CommandTimeout for a SqlDataAdapter?

miker · Answer

I see. You problem may come from Command Timeout property of SqlCommand. By default it sets to 30 seconds. You can change like this: cmdFillTableLineNumberSmallGroupRows.CommandTimeout = 600; //Ten minutes Check out VS Documentation on that subject. However I would look into SQL Server DTS utility for such tasks.

sunspotzzz · Answer

hi Sir I feel that it has got to be the way the amount of data a dataset can store. These are what I have: currently I am using a select statement to select the columns needed and transferring. I got a table B of 6000000 of datarows and 16 columns and I need to transfer data from this table B into another table A according to some constraints. ---------------------------------------------------------------------------------- INSERT INTO sunspot.tableA SELECT * FROM sunspot.TableB WHERE L_LineNumber =  + Temp1.ToString() ---------------------------------------------------------------------------------- It is ok if I execute this for smaller number of rows (20000) but it will display error if I execute for larger number of rows(40000).

miker · Answer

Of course you've got an exception. temp1 = Convert.ToDouble(dsLineNumber.Tables[LineNumberOccurrencesSmall].Rows[Increment] . [0].ToString()); See this dot before [0]? It shouldnot be there... Just delete it. My bad again. Sorry.

sunspotzzz · Answer

Hi I got the error of identifier expected when I compiled the codes. What should I do? Anyway I tried to alter the code and insert an sql exception handler and also directly give L_LineNumber a value. i.e L_LineNumber = 4 ============================================================ string fillLineNumberSmallGroupRows = INSERT INTO sunspot. createdTableLineNumberSmallGroupRows SELECT * FROM sunspot.LINEITEM WHERE L_LineNumber = 4; ============================================================ However I got this error when I execute : ============================================================ An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll Additional information: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. ============================================================ any idea what is wrong with my codes? I feel that there is something wrong with the INSERT Statement. thanks a lot

miker · Answer

Try this. Make sure that there is no ItemArray in this line. temp1 = Convert.ToDouble(dsLineNumber.Tables[LineNumberOccurrencesSmall].Rows[Increment].[0].ToString());