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 Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up
4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank you
On Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:
>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql