How can I use variables when running an SQL query?

August 11, 2023

I am trying to put together the following query:

new_fid_num =  select max(fid)+1 from field;

insert into field values(new_fid_num, 'StartDate', 8, 'The first date that data is available on BMLL', 'T', 'T');

I am not quite sure how to translate it into SQL, I tried something using DECLARE but that doesn’t work. Any ideas how I can achieve the above?

>Solution :

you should be able to use your select statement AS the value such as

insert into field
( explicitColumnName1,
  explicitColumnName2,
  explicitColumnName3,
  explicitColumnName4,
  explicitColumnName5,
  explicitColumnName6 
)
values
( 
  ( select max(fid)+1 from field ),
  'StartDate', 
  8, 
  'The first date that data is available on BMLL', 
  'T', 
  'T'
)