Update table with variables in insert sql

Transact-SQL
1

Hi, I am using sql below to update a table with variable values in insert sql. records are inserted without singlequotes ('). How can I make it work?

declare @config_id varchar(36),
@streamCode varchar(100),
@cycle varchar(20)

set @config_id = '0E6BBCB2-F5AD-4C8A-BF0B-8ADFD1876289'
set @streamCode = '887'
set @cycle = 'xx2019'

	update config set query = 'insert into dataset (id, config_id, streamcode, cycle_id)
	select newid(), '+@config_id 
	+', appl.appl_id, appl.cycle_id from appl 
	inner join cycle on appl.cycle_id = cycle.cycle_id
	inner join pref on appl.appl_id = pref.appl_id  
	inner join stream on pref.course_id = stream.course_id and pref.stream_id = stream.stream_id and appl.num is not null 
	and stream_code = '+@streamCode 
	+'and cycle.cycle_code = '+@cycle 
	where config_id = @config_id
2

Found a solution.
I declared another variable
set @quote = CHAR(39)
update config set query = 'insert into dataset (id, config_id, streamcode, cycle_id)
select newid(), '+ @quote+@config_id+@quote
+', appl.appl_id, appl.cycle_id from appl
inner join cycle on appl.cycle_id = cycle.cycle_id
inner join pref on appl.appl_id = pref.appl_id
inner join stream on pref.course_id = stream.course_id and pref.stream_id = stream.stream_id and appl.num is not null
and stream_code = '+ @quote+@streamCode+@quote
+'and cycle.cycle_code = '+ @quote+@cycle+@quote
where config_id = @config_id

3

hi

triple .... quotes instead of single quote should work

select newid(),'''+@config_id+''',

4

Thanks Harish. Yes, we could do it that way too :slight_smile: