insert records into two tables another after the other

[Reddy]

Hi,

I need to insert records into two tables another after the other. Is the
following code ok or is there a more efficient way of doing this using
sqlcommand object

con.Open()

strSql="insert into table1(column1) values('test1')"
cmd = New OdbcCommand(strSql, con)
cmd.ExecuteNonQuery()
cmd.Dispose()

strSql="insert into table2(column1) values('test2')"
cmd = New OdbcCommand(strSql, con)
cmd.ExecuteNonQuery()
cmd.Dispose()

con.Close()

Thanks,

Srinivas

 

[Miha Markic]

Hi Reddy,

You might execute them in batch if it is supported.
cmd = New OdbcCommand("insert into table1(column1) values('test1');insert
into table1(column1) values('test2')", con)
.....

 

[Hermit Dave]

just write a simle stored proc... pass the values... you dont have to deal
with insert statements and query builder etc... plus if you are using
parameters to pass the stored proc values... you dont have to worry quotes
etcc... it takes care of that...

 

[Michael]

I don't see a reason to dispose of the first command object, just re-set the
sql, and run it again. But if you want the most efficient way, you should
throw sql parameters in the mix, and forget about the dynamic sql, doing
that you'd probably want to throw out the first command.