[rony001]

I am new to sql .please help me with this .
the following sql statement works when i use like this

Dim CmdStr As String = "insert into table1(id,name,ref,amount) select id,name,ref,amount from table where name = A"

How do i use the statement multiple times to insert into multiple tables like this

 Dim CmdStr As String
 CmdStr= "insert into table2 (id,name,ref,amount) select id,name,ref,amount from table where name = B"
 CmdStr="insert into  table3 (id,name,ref,amount) select id,name,ref,amount from table where name = C"
 CmdStr="insert into  table4 (id,name,ref,amount) select id,name,ref,amount from table where name = d"

This post has been edited by Atli: 06 July 2012 - 02:48 AM
Reason for edit:: Please use [code] tags when posting code.

[Atli]

Repeatedly overwriting the CmdStr like that won't work. It'll just end up being whatever you assigned it last. If you want to add to that variable you'd have to use the concat operator.

CmdStr  = "Insert into ... WHERE A;"
CmdStr &= "Insert into ... WHERE B;"
CmdStr &= "Insert into ... WHERE C;"

Whether or not executing multiple statements like that is actually allowed is another matter. You may well have to execute them one at a time.

This also seems like an odd thing to do. I'm guessing you are planing to use this in conjunction with what you are asking in your other thread; creating new tables based on data in an existing table. Which leads me to the same question: Why do you want to do this? Generally you'd want to keep this data in a single table, rather than scattering it over multiple smaller tables.

[rony001]

thank you so much