INSERT,INSERT then UPDATE

Hi all,
I'm making a system in which I store accounts and addresses.
To make the database atomic this info is in different tables because one
account may have many addresses (invoice address, several delivery
addresses, etc). I have put the account as having a primary address stored
in the accounts table. I also tag the account ID onto every address so I can
quickly pull up all the addresses for a particular account.
My problem is.. When I insert a new account I have to insert the address
first missing the account ID (because there isn't one yet), doing this
returns the identity of the address. I can then insert the account with the
primary addresses identity... and now I have inserted the account I can
return the identity of the account to update the address to include the
account id.
Basically my question is... is there an easier way? It seems a bit long
winded.
Thanks
Gav> My problem is.. When I insert a new account I have to insert the address
> first missing the account ID (because there isn't one yet), doing this
> returns the identity of the address.
Can you explain why you have to do it in this order?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||I guess it doesn't really matter which way around I do it, my problem is I
need information in both tables that I don't get until I insert it. I could
just as will insert the account returning the identity then insert the
address whith the account id. returning the address id which i would then
have to update the accounts table to set the identity of the primary
address.
This is done in three steps it could be done in four if i inserted both
records first getting the identity of each, then updating them both to set
the foreign key.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OW2EWsX8DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > My problem is.. When I insert a new account I have to insert the address
> > first missing the account ID (because there isn't one yet), doing this
> > returns the identity of the address.
> Can you explain why you have to do it in this order?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||Hi Gav,
Thank you for using the newsgroup.
As my understanding of your question, you have two tables, one will stored
the information as
AccountID, Account, PrimaryAddress, you have another table address
AccountID OtherAddress, and you have some problem of saving the data, since
there maybe an account without AccountID, etc.
What I would suggest is that, could you save all the information in one
address table' That would be a table includ:
RID, AccountID, Account, Address, Addresstype
Then you also could update the address informaiton according to the
AccountID or the Account.
Hope this helps. If you still have any questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I was hoping that what I was after could be done in one stored procedure...
you see I'm using .NET calls to run the procedures but instead of running
three I would like to run just one.
I'm only used to doing simple statments in the stored procedures. But surely
I can do what I want using a stored procedure.. I guess it would go
something like this:
INSERT INTO ACCOUNTS (AccountName, PrimaryAddress) Values (@.AccountName,
[INSERT INTO ADDRESSES (Address1, address2) VALUES (@.Add1,@.Add2) SELECT
@.@.IDENTITY*]) SELECT @.@.IDENTITY
UPDATE ADDRESSES SET AccountID = @.@.IDENTITY WHERE '?
I'm unsure about this *would this return the new identity for the record and
put it in PrimaryAddress for Accounts? Is there a better way to use
variables (can I store the first return to use in the WHERE parameter on the
second statement)? Like a programming statement will it execute the code is
[] brackets first?
Obviously I'm trying to move the processes away from the code and into the
SQL Procedure if I can I believe it would speed things up slightly.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:n2E56Cg8DHA.3472@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for using the newsgroup.
> As my understanding of your question, you have two tables, one will stored
> the information as
> AccountID, Account, PrimaryAddress, you have another table address
> AccountID OtherAddress, and you have some problem of saving the data,
since
> there maybe an account without AccountID, etc.
> What I would suggest is that, could you save all the information in one
> address table' That would be a table includ:
> RID, AccountID, Account, Address, Addresstype
> Then you also could update the address informaiton according to the
> AccountID or the Account.
> Hope this helps. If you still have any questions, please feel free to post
> message here and I am ready to help.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Gav,
Thank you for your update.
There is no machenism in T-SQL as you described as [ ]. For a relational
database, you should keep the data integrety. So, how about to create a
table as I suggested in my previous post: As you may have a AccountName
without a AccountID, you you need to access its information by AccountName.
How about o keep these in formation in one record:
ID, AccountID, AccountName, Address, AddressContent, AddressType
The AddressType will indicate if it is a primary address or other address
type?
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||this would force me to repeat the account name for each address making the
database bad design. + where would it get the account ID from.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:Ma9QWOu8DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Gav,
> Thank you for your update.
> There is no machenism in T-SQL as you described as [ ]. For a relational
> database, you should keep the data integrety. So, how about to create a
> table as I suggested in my previous post: As you may have a AccountName
> without a AccountID, you you need to access its information by
AccountName.
> How about o keep these in formation in one record:
> ID, AccountID, AccountName, Address, AddressContent, AddressType
> The AddressType will indicate if it is a primary address or other address
> type?
> Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||> need information in both tables that I don't get until I insert it. I
could
> just as will insert the account returning the identity then insert the
> address whith the account id. returning the address id which i would then
> have to update the accounts table to set the identity of the primary
> address.
I don't understand this at all. Can you tell me why this kind of scenario
doesn't work? Paste into Query Analyzer (on a scratch db) and run it...
CREATE TABLE Accounts
(
AccountID INT IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
AccountName VARCHAR(32)
--, etc etc
)
GO
SET NOCOUNT ON
CREATE TABLE Addresses
(
AccountID INT FOREIGN KEY
REFERENCES Accounts(AccountID),
Address VARCHAR(64), -- ?
AddressType INT
)
GO
DECLARE @.AccountID INT
-- create an account for Bob, who has a
-- primary and secondary address
INSERT Accounts(AccountName) VALUES('Bob')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '1 Test St.', 1
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '2 Test St.', 2
-- now, create an account for Frank,
-- who only has a primary address
INSERT Accounts(AccountName) VALUES('Frank')
SET @.AccountID = SCOPE_IDENTITY()
INSERT Addresses(AccountID, Address, AddressType)
SELECT @.AccountID, '3 Test St.', 1
-- see all of the accounts and addresses
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
-- see only the primary addresses of
-- each account
SELECT * FROM Accounts ac
INNER JOIN Addresses ad
ON ac.AccountID = ad.AccountID
AND ad.AddressType = 1
-- you *could* add a constraint or
-- trigger to prevent an account from
-- having more than one primary add.
GO
DROP TABLE Addresses
DROP TABLE Accounts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/