LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » plsql to t-sql

plsql to t-sql

Options

Previous Topic · Next Topic

metro17

Posted: Sunday, November 16, 2008 9:07:44 PM

Rank: Newbie

Joined: 11/16/2008
Posts: 1
Points: 3

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!

bklr

Posted: Wednesday, May 06, 2009 4:07:19 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

try this one
CREATE table t(col1 int identity(1,1) PRIMARY KEY,col2 int)
CREATE table t1(col int identity(1,1),col1 int FOREiGN KEY REFERENCES t(col1),col2 int)
declare @i int

insert into t select 1
select @i = scope_identity()

insert into t1 select @i,1

select * from t

select * from t1

drop table t1
drop table t

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.