Dynamic Variables?

[Kivak]

Hey,

I have two tables of data, table1 and table2. In Table1, there is a
"id" field, a "name" field, and an e-mail field. In table2, there is an
"id" field, and paramters that belong to the certain "id". When I want
to create a new record, I want to make both of the entries at the same
time and have their "id"s match. I thought that the best way of doing
this was to create the data in table1, then get the "id" field of that
entry by way of matching the "name", and then use that id for the "id"
field value for the entry to table2. The problem is this... I don't
know how to collect the value of table1.id and store it in a variable
to be sent with the rest of the data to table2. This is what I tried:

-------------------------------

@name varchar(8000),
@email varchar(8000)
INSERT INTO table1 (name, email)
VALUES (@name, @email)

SELECT id
FROM table1
WHERE table1.name = @name

--------------------------------

From there, I don't know what to do...

[David Portas]

SCOPE_IDENTITY function.

See Books Online for details.

--
David Portas
SQL Server MVP
--

[ZeldorBlat]

@name varchar(8000),
@email varchar(8000)

INSERT INTO table1 (name, email)
VALUES (@name, @email)

INSERT INTO table2 (id, p1, p2, p3)
VALUES (@@identity, 'val1', 'val2', 'val3')

[--CELKO--]

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot be a relational
key. Have you ever seen a CHAR(8000) name or email address? You will
now!

You need to learn how to design an RDBMS and at least learn to use the
right words. Throw this mess out and start over.

[ZeldorBlat]

Celko,

Did you have anything useful to add, or do you just like to hear
yourself talk? I was disapointed that your post didn't include a
shameless plug for your latest book -- although I can't imagine anyone
would pay for the privilege of your abuse when they can get it for free
on the newsgroups.