SQL Returned more than one Value

Question

Im sure this is simple and i will kick myself when i find out but ive been sitting on this problem for the last hour and im getting annoyed with it, could anyone help me.

So i am trying to enter a DeviceId and ConfigurationId Column the values are the primary keys from a Device table and a Configuration table. Yes thats really all the problem is.

I tried using (SELECT ID FROM DeviceId) But that comes up with the error,

Subquery returned more than 1 value

Here is the code i am using the GETDATE()'s are just place holders at the moment and the C.Values are me shredding some XML into the table.

INSERT INTO [Container].[dsc].[DeviceConfiguration]
 ( DateInserted,
  DeviceId,
  ConfigurationId,
  DateRegistered,
  DateRemoved,
  OperatingSystemInstallDate,
  OperatingSystemSerialNumber
 )

SELECT  GETDATE(),
  <This will need to be DeviceId>,
  <This will need to be the ConfigurationId>,
  GETDATE(),
  GETDATE(),
  C.value('@OSInstallDate', 'datetime'),
  C.value('@OSSerialNumber', 'nvarchar(125)')

FROM [test].[HardwareComponent] CROSS APPLY
                                HardwareComponent.ComponentXmlData.nodes('OSData')AS T(C)
  WHERE HardwareComponent.TypeId = 7

Edit: More info sorry, The 2 columns are set as foreign keys.

ALTER TABLE [dsc].[DeviceConfiguration]
    WITH CHECK
    ADD CONSTRAINT FK_DeviceConfiguration_Device
    FOREIGN KEY (DeviceId)
    REFERENCES [dsc].[Device](Id);
GO

ALTER TABLE [dsc].[DeviceConfiguration]
    WITH CHECK
    ADD CONSTRAINT FK_DeviceConfiguration_Configuration
    FOREIGN KEY (ConfigurationId)
    REFERENCES [dsc].[Configuration](Id);
GO

Answer 1

You would need to have primary key ID field in DeviceConfiguration table, and than add below lines after your insert query.

Declare @DeviceId int

Declare @ConfigurationId int

SELECT @DeviceId = DeviceId FROM DeviceConfiguration WHERE ID = @@IDENTITY

SELECT @ConfigurationId = ConfigurationId FROM DeviceConfiguration WHERE ID = @@IDENTITY

And modify your select query as below;

SELECT GETDATE(), @DeviceId, @ConfigurationId, GETDATE(), GETDATE(), C.value('@OSInstallDate', 'datetime'), C.value('@OSSerialNumber', 'nvarchar(125)')

FROM [test].[HardwareComponent] CROSS APPLY HardwareComponent.ComponentXmlData.nodes('OSData')AS T(C) WHERE HardwareComponent.TypeId = 7

Answer 2

In the end i counted the rows in the previous table i needed,

DECLARE @DeviceId = (SELECT COUNT(Id) FROM .....)

...
...
...
SELECT
      GETDATE(),
      (ROW_NUMBER()OVER(ORDER BY Id) % @DeviceId) AS DeviceId,
      ......

And that worked, not quite what i was after but it does the job.