InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:38:26 GMT20http://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxYou can also move the IF up a line to be before the insert statement. You'll need to add the insert again just below your else.IE:IF...insert...select...else...insert...select...That said, there's a good chance that isn't going to do what you want it to do. Hard to say without more information. That exists check is suspect.Wed, 07 Apr 2010 16:02:10 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxLookup the syntax for INSERT INTO..SELECT. You cannot put any external qualifications between the INSERT and the SELECT. If you want to filter it you should do that through either the WHERE clause or the ON clause (if it has JOINs).Wed, 07 Apr 2010 11:14:41 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxI took off the IF before the EXISTS and still got an error message. Any guidance will be appreciated.Wed, 07 Apr 2010 10:56:05 GMTmashikoohttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxOk, Thanks. I'll try it.Wed, 07 Apr 2010 10:05:06 GMTmashikoohttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxAh, now I see. You cannot use an IF construct with an INSERT statement. Look in BOL for INSERT; it must be INSERT INTO....VALUES or INSERT INTO.....SELECT.Wed, 07 Apr 2010 09:59:02 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxThanks. Yes I did actually here is the full syntax, but I still get the error code:declare @trans_num as varchar(10)declare @student as varchar(10)select @trans_num = 'E7281649' --Nomination E numberselect @student = '45854' --Employee number--First change the nomineeUpdate [nominees]set [nominee_no_emp]= @studentwhere id=@trans_numselect * from course_histories where no_sch=(select no_sch from nominations where id=@trans_num) and no_emp=(select nominee_no_emp from nominees where id=@trans_num)select * from course_history_dtl where no_chd_po=@trans_num--Second insert COURSE_HISTORIES entryinsert into course_histories (no_emp, cd_crs, no_ch_seq, no_sch, at_cd_fee, cd_ch_enrolby, dt_ch_compdt, qy_crs_ceu, cd_org, nm_lst_updt, dt_lst_updt, tx_ch_suser1, tx_ch_suser2, tx_ch_suser3, tx_ch_suser4, tx_ch_suser5, no_ch_nuser1, no_ch_nuser2, cd_seg)select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.no_sch as 'no_sch', (case when nom.ob_tuition is null then '0.0' else nom.ob_tuition end) as 'at_cd_fee', 'SYSADM' as 'cd_ch_enrolby', GETDATE() 'dt_ch_compdt', nom.duty_hours as 'qy_crs_ceu', e1.cd_org as 'cd_org', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', e2.tx_emp_suser1 as 'tx_ch_suser1', e2.ad_emp_add1 + '/' + e2.ad_emp_add2 as 'tx_ch_suser2', e2.ad_emp_email as 'tx_ch_suser3', substring(e2.no_emp_phone1,1,3) + substring(e2.no_emp_phone1,5,3) + substring(e2.no_emp_phone1,9,4) as 'tx_ch_suser4', a.contact_name + '/' + a.contact_phone as 'tx_ch_suser5', null as 'no_ch_nuser1', --This field mirrors the class number and is vital for completion on training report replicate('', 6-len(convert(varchar,e2.no_emp_nuser2))) + convert(varchar,e2.no_emp_nuser2) + '.' + replicate('', 6-len(convert(varchar,e2.no_emp_nuser1))) + convert(varchar,e2.no_emp_nuser1) as 'no_ch_nuser2', 'DWD' as 'cd_seg'from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp inner join apprv_group a on nom.apprv_group_id = a.apprv_group_idwhere nom.id=@trans_num --Last insert COURSE_HISTORY_DTL entryinsert into course_history_dtl (no_emp, cd_crs, no_ch_seq, no_chd_po, cd_chd_bstat, cd_chd_btype, dt_chd_invoice, no_chd_regauth, cd_chd_pmeth, nm_lst_updt, dt_lst_updt, id_ch, dt_chd_edate)IF EXISTS (select transaction_date from invoice_log where doc_no=@trans_num) select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.id as 'no_chd_po', 'I' as 'cd_chd_bstat', 'OS' as 'cd_chd_btype', i.transaction_date as 'dt_chd_invoice', replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth', 'SYSADM' as 'cd_chd_pmeth', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', (select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch', GETDATE() 'dt_chd_edate' from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp inner join invoice_log i on nom.id = i.doc_no where nom.id=@trans_numELSE select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.id as 'no_chd_po', 'I' as 'cd_chd_bstat', 'OS' as 'cd_chd_btype', null as 'dt_chd_invoice', replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth', 'SYSADM' as 'cd_chd_pmeth', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', (select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch', GETDATE() 'dt_chd_edate' from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp where nom.id=@trans_numWed, 07 Apr 2010 09:51:17 GMTmashikoohttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxThe syntax looks fine to me. You are using variables in your SQL, did you declare them first?Wed, 07 Apr 2010 09:39:33 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic898639-8-1.aspxIncorrect syntax near the keyword 'IF'. I'm a new SQLServer DBA while trying to wite the code below, I get this error message "Incorrect syntax near the keyword 'IF'." Please help. declare @trans_num as varchar(10)declare @student as varchar(10)select @trans_num = 'E7281649' --Nomination E numberselect @student = '45854' --Employee number--First change the nomineeUpdate [nominees]set [nominee_no_emp]= @studentwhere id=@trans_numselect * from course_histories where no_sch=(select no_sch from nominations where id=@trans_num) and no_emp=(select nominee_no_emp from nominees where id=@trans_num)select * from course_history_dtl where no_chd_po=@trans_num--Second insert COURSE_HISTORIES entryinsert into course_histories (no_emp, cd_crs, no_ch_seq, no_sch, at_cd_fee, cd_ch_enrolby, dt_ch_compdt, qy_crs_ceu, cd_org, nm_lst_updt, dt_lst_updt, tx_ch_suser1, tx_ch_suser2, tx_ch_suser3, tx_ch_suser4, tx_ch_suser5, no_ch_nuser1, no_ch_nuser2, cd_seg)select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.no_sch as 'no_sch', (case when nom.ob_tuition is null then '0.0' else nom.ob_tuition end) as 'at_cd_fee', 'SYSADM' as 'cd_ch_enrolby', GETDATE() 'dt_ch_compdt', nom.duty_hours as 'qy_crs_ceu', e1.cd_org as 'cd_org', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', e2.tx_emp_suser1 as 'tx_ch_suser1', e2.ad_emp_add1 + '/' + e2.ad_emp_add2 as 'tx_ch_suser2', e2.ad_emp_email as 'tx_ch_suser3', substring(e2.no_emp_phone1,1,3) + substring(e2.no_emp_phone1,5,3) + substring(e2.no_emp_phone1,9,4) as 'tx_ch_suser4', a.contact_name + '/' + a.contact_phone as 'tx_ch_suser5', null as 'no_ch_nuser1', --This field mirrors the class number and is vital for completion on training report replicate('', 6-len(convert(varchar,e2.no_emp_nuser2))) + convert(varchar,e2.no_emp_nuser2) + '.' + replicate('', 6-len(convert(varchar,e2.no_emp_nuser1))) + convert(varchar,e2.no_emp_nuser1) as 'no_ch_nuser2', 'DWD' as 'cd_seg'from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp inner join apprv_group a on nom.apprv_group_id = a.apprv_group_idwhere nom.id=@trans_num --Last insert COURSE_HISTORY_DTL entryinsert into course_history_dtl (no_emp, cd_crs, no_ch_seq, no_chd_po, cd_chd_bstat, cd_chd_btype, dt_chd_invoice, no_chd_regauth, cd_chd_pmeth, nm_lst_updt, dt_lst_updt, id_ch, dt_chd_edate)[b]IF EXISTS [/b](select transaction_date from invoice_log where doc_no=@trans_num) select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.id as 'no_chd_po', 'I' as 'cd_chd_bstat', 'OS' as 'cd_chd_btype', i.transaction_date as 'dt_chd_invoice', replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth', 'SYSADM' as 'cd_chd_pmeth', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', (select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch', GETDATE() 'dt_chd_edate' from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp inner join invoice_log i on nom.id = i.doc_no where nom.id=@trans_numELSE select e1.no_emp as 'no_emp', nom.cd_crs as 'cd_crs', '1' as 'no_ch_seq', nom.id as 'no_chd_po', 'I' as 'cd_chd_bstat', 'OS' as 'cd_chd_btype', null as 'dt_chd_invoice', replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth', 'SYSADM' as 'cd_chd_pmeth', 'SYSADM' as 'nm_lst_updt', GETDATE() 'dt_lst_updt', (select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch', GETDATE() 'dt_chd_edate' from nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp) on nom.id = nominees.id inner join employees_dtl e2 on e2.no_emp = e1.no_emp where nom.id=@trans_numWed, 07 Apr 2010 09:29:32 GMTmashikoo