Insert into a table a part of another table

Question

I have two tables, the structure of the first partially recapitulates, iterates the structure of the second:

table1 (id, i, j, k, a, b, c, x, y, z) -- requests
table2 (id, a, b, c, d) -- essential elements / bank subjects

I need to insert into table1 a record from table2 with given ID. What is the best approach to do that?

I have two ideas:

1:

DECLARE @a type, @b type, @c type
SELECT @a = a, @b = b, @c = c, FROM table2 WHERE id = @id
INSERT INTO table1 (i, j, k, a, b, c, x, y, z)
 VALUES (@i, @j, @k, @a, @b, @c, @x, @y, @z)

2:

CREATE TABLE #result (a type, b type, c type)
SELECT a, b, c INTO #result FROM table2 WHERE id = @id
INSERT INTO table1 (i, j, k, a, b, c, x, y, z)
  VALUES (@i, @j, @k,
    (SELECT a FROM #result),
    (SELECT b FROM #result),
    (SELECT c FROM #result),
    @x, @y, @z)

What another approach does exists? Which one is the best-practice?

Answer 1

You can do it with a single insert query:

insert into table1 (a, b, c, d, x, y, z)
select a, b, c, d, @x, @y, @z
from table2
where id = @id

Edit:
With the extra fields that you added it would be:

insert into table1 (i, j, k, a, b, c, x, y, z)
select @i, @j, @k, a, b, c, @x, @y, @z
from table2
where id = @id

Answer 2

Hello abatishchev,

You can use the MySQL GUI Tools, but i don't know if it's possible to do this. http://dev.mysql.com/downloads/gui-tools/5.0.html

Answer 3

I would do it like this:

INSERT INTO table1 (i, j, k, a, b, c, d, x, y ,z)
Select  @i, @j @k, a, b, c, d, @x, @y, @z
From    table2
Where   id = @id

This saves you from getting the data in to local variables and/or temp tables. The performance should be better.

The important part to realize is that you can hard code values in the select. The order in which you list the columns (insert line) must match the order in which you list the columns in the select line.

Answer 4

The temp table could be a benefit through the development process if you are having trouble identifying what data are getting inserted. It could also be useful if you need to perform other data alterations that "require" a cursor to loop through the the records.

Otherwise, it is just another part of your code you would have to update when you add a field.