Subqueries are not allowed in this context for INSERT

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  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.

• matt6288

  SSCertifiable

  Points: 5058

  More actions

  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)

• Lowell

  SSC Guru

  Points: 323522

  More actions

  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!

• PHXHoward

  SSCrazy Eights

  Points: 8389

  More actions

  March 22, 2010 at 10:16 am

  #1137669

  This is perfect. Thank you both so much.