Inserting info in 2 tables in SQLServer

Any SQL gurus around

[korkus2000]

I am joining two tables to fill a dataset from SQLServer. Now I need to find out how to insert the joined info. Is it possible(I would hope) to use an insert statement with a join or do I have to send 2 different inserts to the 2 tables. I have search the web and haven't come up with a good answer.

[duckhunter]

Yes, you will have to do two inserts. Wrap them in a transaction so they rollback if one fails.

blnError = False
adoConnection.BeginTrans

adoConnection.Execute(strSQLInsert1)
adoConnection.Execute(strSQLInsert2)

If adoConnection.Errors.Count > 0 Then
If adoConnection.Errors.Item(0).Number <> 0 Then
blnError = True

' You can loop through the collection of errors if there were more than one
For x = 0 to Ubound(adoConnection.Errors.Item)
strError = strError & "<BR>" & adoConnection.Errors.Item(x).Description
Next
End If
End If

If blnError then
'Failed
adoConnection.RollbackTrans
Response.write strError
else
'Successful
adoConnection.CommitTrans
end if
adoConnection.Close
Set adoConnection = Nothing

'The reason for the nested Error If's is that sometimes you get a warning like "default database changed to db1" which triggers an error count but the error number is 0 so everything was fine with your statements.

[aspdaddy]

Im not 100% sure, but I have a feeling that in SQL 7 and above, views are updatable. This is one of the features that actually defines a RDBMS.

So, if im right, you can use a single qry executed on a view of the join :)

<aaded>
I checked this after posting and it can be done, but there are some restrictions.

Theres also another alternative I found called an "instead of trigger" ..
</added>

[markusf]

duckhunter has the best solution. You want to avoid as much overhead to the database as possible so that you don't get deadlocks or long running blocking queries. A updatable view, or views in general for that matter are EXTREMELY bad and should be avoided.

[duckhunter]

I work with a large scale ASP/SQL implementation and "Implicit Transactions" are more often the root of Database issues. It is much more efficient to Set and Commit your own transactions.

It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs.

last sentence of:
[msdn.microsoft.com ]

[aspdaddy]

>views in general for that matter are EXTREMELY bad and should be avoided.

Mark,
I dont know why you think that, coz I have used views on Oracle & SQL and they solve a lot of the problems of complex queries in large normalised datbases. Once your views are done, the ASP is really simple.

Defining the views is also an important part in designing any large application and separates the data and business logic.

If its a site with levels of access you can define views with the same name but different owners, so that a view gives different data depending on who is using it.

>You want to avoid as much overhead to the database as possible

This might be the case with Access, but with SQL/Oracle you want to be putting the data tasks on the db server not the application server, and using its features, thats whats its there for.

Scenarios for Using Views [msdn.microsoft.com]