Insert sql syntax error

1

Hello, I'm attempting to insert this record into a new export I'm creating. Some lines I've customized and have inserted fine, but this one which I've copy from a previous export with the exception of the export id (1042) I'm having no luck with.
Here is the sql:
Insert into TS_EXP_DEF_SQL Values (1042,'VANILLA','Y','TS_ORDER.ORDER_ID NOT IN (SELECT TS_ORDER','ORDER_ID FROM TS_ORDER,CSM_SECURITY WHERE TS_ORDER.SEC_ID',18,31,'=','AND','SYB','N','CSM_SECURITY.SEC_ID AND TS_ORDER.INV_CLASS_CD = ''OPTN'' AND CSM_SECURITY.LIST_EXCH_CD IN (''XOTC'',''OTC'')))'

and here is the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CSM_SECURITY.SEC_ID AND TS_ORDER.INV_CLASS_CD = 'OPTN' AND CSM_SECURITY.LIST_EXCH_CD IN ('XOTC','OTC')))'.

I've tried numerous alternatives with 1, 2, 3 tics around the text but nothing I've tried is working. Any ideas?

2

That's not valid SQL Server syntax. Did you copy if from a different dbms?

3

Not, it's from the same dbms and this particular line I'm attempting to insert verbatim and it's the only record out of approx 40 that is giving me problems.

4

Sorry, you're quite right! You just need to add a closing right paren:

Insert into TS_EXP_DEF_SQL Values (1042,'VANILLA','Y','TS_ORDER.ORDER_ID NOT IN (SELECT TS_ORDER','ORDER_ID FROM TS_ORDER,CSM_SECURITY WHERE TS_ORDER.SEC_ID',18,31,'=','AND','SYB','N','CSM_SECURITY.SEC_ID AND TS_ORDER.INV_CLASS_CD = ''OPTN'' AND CSM_SECURITY.LIST_EXCH_CD IN (''XOTC'',''OTC'')))')

1 Like
5

That worked like a charm. Thanks much for your help.

6

Hello, hope you can also help with one last line. This trade export is only needed for certain currencies so I'm trying to insert one last record to complete the export but again getting a syntax error: Here is the sql I'm attempting:
Insert into TS_EXP_DEF_SQL Values (1042,'VANILLA','Y','TS_ORDER','PRIN_LOCAL_CRRNCY',24,40,'IN','AND','SYB','N','('TWD','KRW','BRL','EGP','THB','IDR','INR','MYR','PHP','CLP','CNY','RUB','COP')'

I've tried various using two and three tics and single, multiple close parens but thus far nothing is working.

7

I should also point out that it appears to be the last columns with the list of currencies that is the issue.
'('TWD','KRW','BRL','EGP','THB','IDR','INR','MYR','PHP','CLP','CNY','RUB','COP')'

8

You need to double-up all the single-quotes WITHIN that column (not the start / end ones for that column)

You are missing a closing parenthesis

(
	1042,'VANILLA','Y','TS_ORDER','PRIN_LOCAL_CRRNCY',24,40,'IN','AND','SYB','N',
	'(''TWD'',''KRW'',''BRL'',''EGP'',''THB'',''IDR'',''INR'',''MYR'',''PHP'',''CLP'',''CNY'',''RUB'',''COP'')'
)

... but ...

I would recommend storing those individual delimited values in a child-table, rather than all within a single column. Finding any row that contains, say, 'CLP' is going to be very inefficient, let alone any analysis like "How many TS_EXP_DEF_SQL rows have "CLP"? or a Pivot table of TS_EXP_DEF_SQL ID & Description (say) with individual columns of all the actual values found such as TWD, KRW, ... containing a Count / whatever.

9

Hmmm ... looking back at your first question it looks like you were storing a SQL statement (and the tablename hints at that too ...) so not sure what the individual columns are in your final question, but presumably a similar requirement. So maybe analysis of the 'XXX', 'YYY' list is not relevant after all ...

I recommend that you put a Column List in your INSERT statement:

Insert into TS_EXP_DEF_SQL 
(
   ColName1, Colname2, ...
)
Values (1042,... )

otherwise if the structure of the table changes in future (e.g. new column added, or (God Forbid!!) sequence of the columns changed), your INSERT statement will fail.

10

Hey Kristen..thanks for taking a look. I just get this one to work and again I was missing the closed parens..