2 sub queries in insert statement values in MS SQL Server Compact Edition 4.0

Question

Specifically I'm using SQL Server Compact 4.0, if that makes a difference. I have 3 tables (note,userTable,verse). the user and verse table have no correlation except in this note table, so I can't do a single subquery joining the two tables.

INSERT INTO [note]
           ([verse_id]
           ,[user_id]
           ,[text]
           ,[date_created]
           ,[date_modified])
     VALUES
           ( (SELECT Id FROM verse 
                WHERE volume_lds_url = 'ot' 
                AND book_lds_url = 'gen' 
                AND chapter_number = 8
                AND verse_number = 16)
           , (SELECT Id FROM userTable
                WHERE username = 'canichols2')
           ,'test message'
           ,GETDATE()
           ,GETDATE());
GO

As far as I can tell, the statement should work. The outer statements works fine if i hard code the Foreign Key values, and each of the subqueries work as they should and only return one column and one row each.

Error Message:There was an error parsing the query. [ Token line number = 8,Token line offset = 14,Token in error = SELECT ]

So It doesn't like the subquery in a scalar values clause, but I Can't figure out how to use a

INSERT INTO .... SELECT ....

statement with the 2 different tables.

---

Table Definitions

Since @Prasanna asked for it, here's the deffinitions

CREATE TABLE [userTable] (
  [Id] int IDENTITY (1,1)  NOT NULL
, [username] nvarchar(100)  NOT NULL
, [email] nvarchar(100)  NOT NULL
, [password] nvarchar(100)  NULL
);
GO
ALTER TABLE [userTable] ADD CONSTRAINT [PK_user] PRIMARY KEY ([Id]);
GO
CREATE TABLE [note] (
  [Id] int IDENTITY (1,1)  NOT NULL
, [verse_id] int  NULL
, [user_id] int  NULL
, [text] nvarchar(4000)  NOT NULL
, [date_created] datetime DEFAULT GETDATE() NOT NULL
, [date_modified] datetime NULL
);
GO
ALTER TABLE [note] ADD CONSTRAINT [PK_note] PRIMARY KEY ([Id]);
GO
CREATE TABLE [verse] (
  [Id] int IDENTITY (1,1)  NOT NULL
, [volume_id] int  NULL
, [book_id] int  NULL
, [chapter_id] int  NULL
, [verse_id] int  NULL
, [volume_title] nvarchar(100)  NULL
, [book_title] nvarchar(100)  NULL
, [volume_long_title] nvarchar(100)  NULL
, [book_long_title] nvarchar(100)  NULL
, [volume_subtitle] nvarchar(100)  NULL
, [book_subtitle] nvarchar(100)  NULL
, [volume_short_title] nvarchar(100)  NULL
, [book_short_title] nvarchar(100)  NULL
, [volume_lds_url] nvarchar(100)  NULL
, [book_lds_url] nvarchar(100)  NULL
, [chapter_number] int  NULL
, [verse_number] int  NULL
, [scripture_text] nvarchar(4000)  NULL
);
GO
ALTER TABLE [verse] ADD CONSTRAINT [PK_scriptures] PRIMARY KEY ([Id]);
GO

I'm aware it's not in the 1st normal form or anything, But that's how it was given to me, and I didn't feel like dividing it up into multiple tables.

SubQuery Results

To show the results and how there's only 1 row.

SELECT Id FROM WHERE volume_lds_url = 'ot'
    AND book_lds_url = 'gen' 
    AND chapter_number = 8
    AND verse_number = 16

Id
200

And the second subquery

SELECT Id FROM userTable
    WHERE username = 'canichols2'

Id
1

Answer 1

Attention: The target system is SQL-Server-Compact-CE-4

This smaller brother seems not to support neither sub-selects as scalar values, nor declared variables. Find details in comments...

Approach 1

As long as you can be sure, that the sub-select returns exactly one scalar value, it should be easy to transform your VALUES to a SELECT. Try this:

INSERT INTO [note]
       ([verse_id]
       ,[user_id]
       ,[text]
       ,[date_created]
       ,[date_modified])
 SELECT
        (SELECT Id FROM verse 
            WHERE volume_lds_url = 'ot' 
            AND book_lds_url = 'gen' 
            AND chapter_number = 8
            AND verse_number = 16)
       , (SELECT Id FROM userTable
            WHERE username = 'canichols2')
       ,'test message'
       ,GETDATE()
       ,GETDATE();

Approach 2

No experience with Compact editions of SQL-Server, but you might try this:

DECLARE @id1 INT=(SELECT Id FROM verse 
            WHERE volume_lds_url = 'ot' 
            AND book_lds_url = 'gen' 
            AND chapter_number = 8
            AND verse_number = 16);

DECLARE @id2 INT=(SELECT Id FROM userTable
            WHERE username = 'canichols2');

INSERT INTO [note]
       ([verse_id]
       ,[user_id]
       ,[text]
       ,[date_created]
       ,[date_modified])
 SELECT @id1
       ,@id2
       ,'test message'
       ,GETDATE()
       ,GETDATE();