Copy from one table to another based on condition

[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"

I want to copy rows from one table into multiple tables

  Dim CmdStr As String 

        CmdStr  = "insert into table2 (id,name,ref,amount) select id,name,ref,amount from table where name = A"
      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"

Thanks in advance

[BitBlt]

Seems like you're mixing and matching your questions here. Notwithstanding the code syntax error (can we assume since you're using "DIM" then it's VB?) and the unwise use of reserved words ("table" and "name"), you aren't separating your statements with either a statement terminator (such as ";" or a "GO" statement...that's dependent on what DBMS or flavor of SQL you're using). Next, your string concatenation has left no space or line break between one statement and the next.

If it's VB and MS SQL Server try this:

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

Another thing you need to worry about is where the values for A, B, C and D are coming from. The way you have it coded, the SQL engine is going to error out because it's looking for a variable somwhere in the context of your SQL batch. If you are trying to get them from some control somewhere, then you need to explicitly concatenate those values in (with appropriate delimiters).

Hope this helps. Good luck!

[msi]

You can't insert values in a number of tables with single statement.
Use a statement for each table,
By the way,
where name = 'A'
I suppose.

[JorgeM]

If you are sw to SQL, focus on getting the correct syntax for one Insert Into statement first. Then I would also suggest you break this up I to manageable seperated statements. You also may want to consider developing stored procedures so you can simply pass parameters from your app.

[rony001]

Thanks all for your reply ,

[BitBlt]

Thanks for your acknowledgement...but did your question get solved? If so, please let us (and all future querents) know how you solved your issue. This site is about SOLUTIONS, not just REPLIES.

If you were able to solve your problem but didn't solve it with the replies you received, tell the community how you did it.

Thanks!