Need help with INSERT statement with sub-query

Question

Post reply

Need help with INSERT statement with sub-query

Tuan H. Le

SSC Eights!

Points: 806
More actions
August 18, 2006 at 11:05 am

#119326

Hi,
The following insert statement was not compiled.
       INSERT INTO my_table (comments,
                                       sr_status,
                                       sr_notes_count,
                                     sr_activity_count,
                                       last_updated_dt)
        VALUES ('some input comments',
                    (SELECT status FROM my_table_2 WHERE id = 123)
                    (SELECT count(*) FROM my_table_3 WHERE id = 123)
                    (SELECT count(*) FROM my_table_4 WHERE id = 123)
                    GETDATE())
Is there a way to use sub-query within an INSERT statement?
THanks,
Tuan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 1

subqueries can be used in values clause of insert statement, but it should return only single value

Tuan H. Le SSC Eights! Points: 806 More actions · Answer 2

all my sub-queries return a single value. But my statement didn't compile. How do I correct the above statement?

THanks,

Tuan

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

try:

INSERT INTO my_table (comments,

sr_status,

sr_notes_count,

sr_activity_count,

last_updated_dt)

SELECT 'some input comments',

(SELECT max(status) FROM my_table_2 WHERE id = 123),

(SELECT count(*) FROM my_table_3 WHERE id = 123),

GETDATE()

You may also need to account for nulls

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 4

I guess it will work , do not use values clause

try:

INSERT INTO my_table (comments,

sr_status,

sr_notes_count,

sr_activity_count,

last_updated_dt)

SELECT 'some input comments',

(SELECT status FROM my_table_2 WHERE id = 123),

(SELECT count(*) FROM my_table_3 WHERE id = 123),

GETDATE()

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Not really sure but I think it is a simple case of missing comma's. Compare this insert to the original and let us know.

INSERT INTO my_table (comments,

sr_status,

sr_notes_count,

sr_activity_count,

last_updated_dt)

VALUES ('some input comments',

(SELECT status FROM my_table_2 WHERE id = 123),

(SELECT count(*) FROM my_table_3 WHERE id = 123),

(SELECT count(*) FROM my_table_4 WHERE id = 123),

GETDATE())

hth,

Lynn

Sergiy SSC Guru Points: 110208 More actions · Answer 6

You don't need subquery at all.

For better performance always try to build "one go" query.

INSERT INTO my_table (comments,

sr_status,

sr_notes_count,

sr_activity_count,

last_updated_dt)

SELECT 'some input comments', T2.status,

count(T3.id), count(T4.id), GETDATE()

FROM my_table_2 T2

INNER JOIN my_table_3 T3 ON T2.id = T3.id

INNER JOIN my_table_4 T4 ON T2.id = T4.id

GROUP BY T2.Id, T2.status

WHERE T2.id = 123

And better forget about VALUES clause. Forever.

_____________
Code for TallyGenerator