INSERT INTO a Table FROM another Table

sunspotzzz

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_OrderKey,L_ PartKey,L_SuppKey,L_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.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

sunspotzzz22y ago
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?
+1
miker22y ago
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.
+1
sunspotzzz22y ago
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 6,000,000 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).
+1
miker22y ago
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.
+1
sunspotzzz22y ago
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
+1
miker22y ago
Try this. Make sure that there is no "ItemArray" in this line. temp1 = Convert.ToDouble(dsLineNumber.Tables["LineNumberOccurrencesSmall"].Rows[Increment].[0].ToString());
+1