Post reply

Subqueries are not allowed in this context for INSERT

  • March 22, 2010 at 9:01 am

    #230990

    I've written an insert query that is supposed to put yesterday's date into the first column then count yesterday's records in three tables and put the totals into columns. Unfortunately, I receive the following error:

    Subqueries are not allowed in this context. Only scalar expressions are allowed.

    Here is the SQL

    INSERT INTO database2..table1 (Date, Emails1, Emails2, Emails3)

    VALUES

    (getdate() -1,

    (SELECT count(*) FROM database1..table1 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1),

    (SELECT count(*) FROM database1..table2 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1),

    (SELECT count(*) FROM database1..table3 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1))

    Thanks for helping if you have any suggestions.

  • March 22, 2010 at 9:07 am

    #1137635

    Don't insert values but use a select statement:

    Untested but this should work:

    INSERT INTO database2..table1 (Date, Emails1, Emails2, Emails3)

    SELECT GETDATE()-1,

    (SELECT count(*) FROM database1..table1 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1),

    (SELECT count(*) FROM database1..table2 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1),

    (SELECT count(*) FROM database1..table3 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1)

  • March 22, 2010 at 9:08 am

    #1137636

    slight formatting change, added a few aliases, this is syntactically correct:

    INSERT INTO database2..table1 (Date, Emails1, Emails2, Emails3)

    SELECT getdate() -1 As TheRightDate,X1.CNT,X2.CNT,X3.CNT

    FROM

    (SELECT count(*) AS CNT FROM database1..table1 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1)X1,

    (SELECT count(*) AS CNT FROM database1..table2 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1)X2,

    (SELECT count(*) AS CNT FROM database1..table3 WITH (NOLOCK) WHERE datediff( d,INSERT_DATE,getdate()) = 1)X3

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 22, 2010 at 10:16 am

    #1137669

    This is perfect. Thank you both so much.

Viewing 4 posts - 1 through 3 (of 3 total)

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