CODEWITHSUNDEEP
sqlsql-servertemp-tables
abatishchev
abatishchev

Reputation: 100248

Insert into a table a part of another table

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?

Upvotes: 0

Views: 1041

Answers (4)

Nathan Campos
Nathan Campos

Reputation: 29497

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

Upvotes: -2

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 1

Guffa
Guffa

Reputation: 700242

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

Upvotes: 2

George Mastros
George Mastros

Reputation: 24498

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.

Upvotes: 6

Related Questions