CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user988122
user988122

Reputation: 19

Stored procedure (insert data into a table and then insert the generated id into another) SQL Server

I want to insert data into table 1, then I want to insert the generated idAddress into table 2, how can I do this?

Table 1 PK-idAddress, street, cp

Table 2 PK-idUser, FK-idAddress,

Thanks in advance.

Upvotes: 1

Views: 661

Answers (1)

Nonym
Nonym

Reputation: 6299

Try this out:

INSERT INTO table1(STREET, CP) VALUES('THIS', 'THAT')
INSERT INTO table2(FK-IDADDRESS) VALUES(@@IDENTITY) -- OR SCOPE_IDENTITY

Well, since I'm not sure of your tables structure and of your full code, that's just an assumption up there (my code, I mean) -- Basically, it's calling @@IDENTITY or SCOPE_IDENTITY -- read into the links below to get an idea of what you might really need.

SCOPE_IDENTITY

@@IDENTITY

DIFFERENCE BETWEEN...

There's another method you can use:

IDENT_CURRENT

And you can read this too:

http://sqlserverpedia.com/wiki/Functions_-_@@IDENTITY,_SCOPE_IDENTITY,_IDENT_CURRENT

*credit goes to @JakeFeasel for bringing up IDENT_CURRENT

Upvotes: 2

Related Questions