Insert Into with subquery to insert multiple rows into database

Question

I am struggling to generate a 'notifications' query to work correctly at the moment.

I'm working on a query that will add a string to a table in which will add multiple rows at once, with only one column of which will be changed in this 'bulk insert':

I currently have:

    public static void addNotification(string notification)
    {
        SqlConnection scon = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);

        using (SqlCommand scmd = new SqlCommand())
        {
            scmd.Connection = scon;
            scmd.CommandType = CommandType.Text;
            scmd.CommandText = "INSERT INTO notificationAck (Email, Notification, isAcknowledged) VALUES ((SELECT Email FROM AspNetUsers), '" + notification + "' ,0)";
            scon.Open();
            int status = scmd.ExecuteNonQuery();
            scon.Close();
        }
    }

However, on passing this a string like: "this is a notification", it comes up with the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

So, could someone possibly explain how this query should be run?

The table has the columns:

enter image description here

I understand the 'PK1' isn't the best of practises, but this is only a table for testing purposes (it's set to auto increment)

So, the isAcknowledged should be set to 0, the notification should be this string, and the email should be selected from each row of the table AspNetUsers?

Would anyone e able to explain to me how I should go about adding this data into my db?

INSERT iNTO SELECT does not require VALUES: w3schools.com/sql/sql_insert_into_select.asp — Giorgos Betsos
– Giorgos Betsos, Commented Mar 23, 2015 at 16:47

Giorgos Betsos · Accepted Answer · 2015-03-23 16:49:56Z

2

Something like this should work:

scmd.CommandText = "INSERT INTO notificationAck (Email, 
                                                 Notification, 
                                                 isAcknowledged) 
                    SELECT Email, '" + notification + "', 0
                    FROM AspNetUsers";

INSERT INTO SELECT does not require VALUES. Hence, you only have to use a SELECT statement that retrieves Email field from AspNetUsers plus two constant values.

answered Mar 23, 2015 at 16:49

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jbutler483 Over a year ago

Thank you for your explanation. Reiterating what I've also commented, I didn't realise I had to place the table name at the end of the statement, not within it

Giorgos Betsos Over a year ago

@jbutler483 You' re welcome. Please also note the you should use an SqlParameter object in place of notification variable. Your code is prone to SQL Injection.

jbutler483 Over a year ago

Admin controls only here :)

Tanner · Accepted Answer · 2015-03-23 16:50:23Z

2

You don't want to use VALUES when using a query to generate your results. You can change the INSERT query to this:

INSERT INTO notificationAck (Email, Notification, isAcknowledged) 
SELECT Email, 'SOME STRING', 0 FROM AspNetUsers

Which translated in to your code would look like:

scmd.CommandText = "INSERT INTO notificationAck (Email, Notification, isAcknowledged) 
                    SELECT Email, '" + notification + "', 0 FROM AspNetUsers";

answered Mar 23, 2015 at 16:50

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

1 Comment

jbutler483 Over a year ago

Thank you for your prompt response. I didn't realise I had to place the table name at the end, which was causing an issue as well. So thank you :)

A_Sk · Accepted Answer · 2015-03-23 16:53:14Z

0

Select Top 1 in Sub Query if you want to insert only 1 row

try

INSERT INTO notificationAck (Email, Notification, isAcknowledged) 
VALUES ((SELECT top 1 Email FROM AspNetUsers), '" + notification + "' ,0)

if you want to insert all rows from AspNetUsers then use

 INSERT INTO notificationAck(Email,Notification, isAcknowledged) select 
 Email,''+notification +"',1 from AspNetUsers

answered Mar 23, 2015 at 16:53

A_Sk

4,6505 gold badges31 silver badges55 bronze badges

1 Comment

jbutler483 Over a year ago

thank you for your answer. However, I needed to insert for each email returned.

Atilla Ozgur · Accepted Answer · 2015-03-23 17:00:47Z

0

Your query

SELECT Email FROM AspNetUsers

returns more than one row as your error says. You need to change it so that it returns only one row, for example:

SELECT TOP 1 Email FROM AspNetUsers

Then change your command as below

scmd.CommandText = "INSERT INTO notificationAck (Email, Notification, isAcknowledged) SELECT TOP 1 Email " + notification + "' ,0 FROM AspNetUsers'";

if you want to insert more than one row, the remove top 1 from query.

scmd.CommandText = "INSERT INTO notificationAck (Email, Notification, isAcknowledged) SELECT Email " + notification + "' ,0 FROM AspNetUsers'";

edited Mar 23, 2015 at 17:00

answered Mar 23, 2015 at 16:52

Atilla Ozgur

14.8k3 gold badges54 silver badges75 bronze badges

2 Comments

jbutler483 Over a year ago

thank you for your answer. However, I needed to insert for each email returned.

Atilla Ozgur Over a year ago

Okay. It seems that I misunderstood your question. You are right of course.

Collectives™ on Stack Overflow

Insert Into with subquery to insert multiple rows into database

4 Answers 4

3 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related