Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

Insert unique rows in temp table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

I don't think what you are doing is valid because a local temp table scope is very limited, but if it valid it will be covered in the link below by one of the best minds in T-SQL. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1