Subquery cannot appear in an Insert Values statemement

[jez]

Hi.

The title of this thread is the error i get from the following sql statement.

INSERT INTO Media_Cart_Items
(cart_id, cart_item_filename, cart_item_filesize, cart_item_full_path)
VALUES     ('286', '***.WMV',
                          (SELECT     Filesize
                            FROM          Media_Delivery
                            WHERE      Filename = '***.WMV'), '**.WMV');

Can anyone tell me how you would acheive this with MS SQL, as this statement would work in other DBs like Oracle etc, so i thought there must be something like it in MS SQL.

Suggestions welcome.

 

[r937]

INSERT 
  INTO Media_Cart_Items
     ( cart_id
     , cart_item_filename
     , cart_item_filesize
     , cart_item_full_path )
SELECT '286'
     , '***.WMV'
     , Filesize
     , '**.WMV' )
  FROM Media_Delivery
 WHERE Filename = '***.WMV'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jez]

Thanks !! That works just fine.

 

[r937]

actually, the SELECT clause has an erroneous trailing parenthesis, sorry

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jez]

Yes, i saw that and removed it for my first test and it worked so....

Thanks,

Jez