Insert problem

[Blue_Skye]

I want to take all the data from a column of a table. Foreach value i get, I used it as a field to my insert function into another table . I some kind like for(int i=0;i

[Luis Alonso Ramos]

Yes, you could do it very easily like this: string sql = "INSERT INTO Table2 VALUES (@value1, .....)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@value1", SqlDbType.Int); // or whatever type foreach(DataRow row in table1.Rows) // I like it better than for in this case { cmd.Parameters[0].Value = row[1]; // Second column into the parameter cmd.ExecuteNonQuery(); // Make the insert } It can be done with a stored procedure in the same way, only if you change the sql variable to the name of a stored procedure that takes one parameter named value1. I hope it helps. -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!

[Blue_Skye]

I means that we'll do all that things in a single store procedure, not in C# (include the way we take each row from a table). And then, in C#, we just call that store procedure to execute automatic. Can you help me? Thanks your reply. :)

[Luis Alonso Ramos]

Ah I see. You could try this:

INSERT INTO Table2 (valueFromTable, fixedColumn1, ...) SELECT column1, fixedValue1 ... FROM Table1

I'm no SQL expert, but if I remember correctly, I once used something like that to copy a table. -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!

[toxcct]

it won't work until you don't provide a values statement into your insert

---

TOXCCT >>> GEII power
[toxcct][VisualCalc]

[Blue_Skye]

I've just found the solution. The way i solve the problem is using cursor : declare @VariableName datatype declare CursorName for select TableColumn from Table1 open CursorName fetch next from CursorName into @VariableName while(@@FETCH_STATUS = 0) begin Insert into Table2 values(@VariableName,...,...) fetch next from CursorName into @VariableName end close CursorName deallocate CursorName The example i used here take just one column but you can use as many columns as you like. This cursor remind me while(Reader.Read()) in C# :) Thanks your reply. I'm very happy to talk with you :)

[Luis Alonso Ramos]

Yes, it works. I've used the following statement in a stored procedure:

INSERT INTO Shutters(ID_pedido, Fabricado, Precio, Descripcion, Tipo, Madera, Color, Acabado)
    (
        SELECT @ID_pedido, 0, Precio, Descripcion, Tipo, Madera, Color, Acabado
            FROM Cotizacion_Shutters
            WHERE ID_pedido = @ID
    )

and it works as expected, copying some records from one table to another with most columns the same, and changing only the values of one column (Fabricado, set to 0). -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!

[Luis Alonso Ramos]

I'm glad you solved it, but cursors are very inefficient. I would recommend you at least try my idea, it should work much faster. See my response to toxcct above; the code I gave you works. -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!

[Blue_Skye]

Wow, it's too strange and really interesting :laugh:. I've never seen it before (even in SQL Help). You're right, it really works... Thanks a lot! :)

[Luis Alonso Ramos]

Blue_Skye wrote: You're right, it really works... I know, I learned it here! :-D I'm glad to have helped you. Good luck! :) -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!