-1

I have two tables in two separate databases. I'm trying to take all of the employee IDs that were inserted into the employee database yesterday and input them into our purchasing database with an additional column containing a role within our purchasing platform.

DECLARE @today date = GETDATE();

INSERT INTO dbo.PurchasingDB(UserId, RoleId)
VALUES 
((SELECT EMPLOYEE_ID FROM
Employee.dbo.EMPLOYEE_INFO
WHERE DATE_LOADED >= DATEADD(DAY, -1, @today)
  AND DATE_LOADED <  @today)
  ,'REQUESTOR');

The problem that I'm having is that I receive the following error when I run the statement above:

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

I guess there are a few questions here:

  1. Is there a simpler way to retrieve the rows that were entered into the employee database yesterday?
  2. What, if any, are the alternatives to using the >= and < operators when trying to do a comparison, or in this case, retrieve yesterdays date.
  3. Am I going about this the wrong way? Would it make sense to do separate SQL statements?
Keaton Palmer
  • 9
  • 2
  • You really need to read the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15) for `Insert`, drop the `values` keyword. – Stu Mar 02 '22 at 16:11
  • `DW_LOADED_DTTM >= DATEADD(DAY, -1, @today)` Beware - this does not do what you think. – SMor Mar 02 '22 at 17:30
  • Made a small edit to the code to make it slightly more generic. Thanks @Stu. I had searched online various ways to make that work, but should have read through the entire documentation, as all examples I had looked at included the `values` keyword. – Keaton Palmer Mar 02 '22 at 18:42
  • @KeatonPalmer So does the suggested answer solve your problem? – Stu Mar 02 '22 at 18:45
  • 1
    @SMor Care to explain why? I see numerous results on SO and other sources online that do what was posted. – Keaton Palmer Mar 02 '22 at 18:46
  • 1
    You may want to add a check to ensure the user doesn't already exist in your db, but the date check looks okay to me since it's cast as a `date`. If it were a `datetime`, then yes it would be incorrect `datetime` https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a222fe04df4087bf15e76a7e599c076 – SOS Mar 02 '22 at 18:58

1 Answers1

2

You can insert using the result of a select statement, such as

insert into dbo.PurchasingDB(UserId, RoleId)
select EMPLOYEE_ID, 'REQUESTOR'
from Employee.dbo.EMPLOYEE_INFO
where DW_LOADED_DTTM >= DateAdd(day, -1, @today)
 and DW_LOADED_DTTM <  @today
Stu
  • 30,392
  • 6
  • 14
  • 33