plsql to t-sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

plsql to t-sql

Author

Topic

metro17
Starting Member

8 Posts

Posted - 2008-11-16 : 22:09:36

DECLARE
nId NUMBER;
BEGIN
INSERT INTO TABLE A (name,age) VALUES
('X',12)
RETURNING
ID INTO nId;
INSERT INTO TABLE B (id,address,marital status) VALUES
(nId,'at home', 'married');
END;
Can anyone please convert the above code in sql to a T-sql "PROCEDURE" please . Thanks....

I have two tables A and B . I am inserting rows into table A(id,name,age,location) and when i do this it autogenerates a number under the ID column and is the primary key for that table.
Now I have to use this column to insert rows into table B(id,address,marital status) . I have to write a stored procedure for this . Can anyone help me around this as initially I had a single table and with all the columns under the same table . but now the db design has been made in this way spliting the table into two tables and when I insert into table A it autogenerates ID column and now I have to use this ID to insert into table B .
The mentioned example is only a sample.This is what I am trying to achieve.

I am inserting values into table A and there is one column in tableA which autogenerates values and forms the primary key of table A.Now using this column I have to insert values into table B including the autogenerated column values in Table A into table B under a under a column along with some other column values.

Eg:
Table A col1 , col2, col3 col1 is autogenerated and forms the primary key.
Table B col1 , col2, col3
I have to use col 1 in table A and insert into table B values under col 2 and col 3

Eg:
Table A
1 AB NY
2 BC DC
3 CD MA
Table B
1 ASCE 100
2 NJNJ 200
3 GUGN 300

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-16 : 22:50:41

[code]CREATE PROC enterdata
AS
BEGIN
DECLARE @nId int

INSERT INTO TABLE A (name,age)
VALUES ('X',12)
SET @nId=SCOPE_IDENTITY()
INSERT INTO TABLE B (id,address,marital status)
VALUES (@nId,'at home', 'married')
END;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:17:51

Also understand various ways by which you can get autogenerated id value in T-sql
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources