SQL and Database: Inserting into a table where one value is the same

Okay, I’ve been trying to figure out how to do an insert into a table where one column’s value is always the same, but the second value will be from a select statement.

Something like:

INSERT INTO <table_name> (column1, column2)
VALUES ('055', (SELECT DISTINCT <column_name> FROM <source_table> WHERE <various conditions>)

The code above, obviously doesn’t work. I get an error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

Never mind. I figured it out.

INSERT INTO <table_name> (column1, column2)
(SELECT DISTINCT <column_name>, '055' FROM <source_table> WHERE <various conditions>)

This seems to work!

Yep. In your first example, you’re attempting to insert a single record, but the select is returning multiple records.

In the second example, you’re inserting a set into your table - one or more records.

Watch out though, if there are triggers on your table. I think the last time I did that sort of operation, the trigger didn’t fire the way I expected. It might be a poorly-constructed trigger though.