user8491385 user8491385 - 1 year ago 134
R Question

Pasting data to SQL Query

This is driving me pretty nuts but cannot get it to work.

I want to paste the following into a SQL query within R.

UKWinnersID<-c("'1WKX6'", "'ULTY8'", "'JNZX0'", "'8J4D8'",
"'KZJAJ0'", "'W8BH47'", "'CP8RPW9'", "'52TD5'", "'TLKV4'")



sqlQuery(myConn, paste("SELECT TOP 10000 [AxiomaDate]
,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
FROM [PortfolioAnalytics].[Data_Axioma]
Where AxiomaID IN (",(paste(UKWinnersID, collapse = ","),")")))


I am using the paste function now which doesn't seem to work. Anyone got an idea? The input for the last line would be like this (with the data stored in a) for the query to work..

AxiomaID IN ('1WKX6', 'ULTY8', 'JNZX0', '8J4D8',
'KZJAJ0', 'W8BH47', 'CP8RPW9', '52TD5', 'TLKV4')


The current output is

sqlQuery etc Where AxiomaID IN ( '1WKX6','ULTY8','JNZX0','8J4D8','KZJAJ0','W8BH47','CP8RPW9','52TD5','TLKV4' )'"

Essentially I want to remove the last two characters in this paste which is a ' and ".

Answer Source

I'm not able to recreate your exact output. Try the following; it produces the SQL code I would expect.

UKWinnersID<-c("'1WKX6'", "'ULTY8'", "'JNZX0'", "'8J4D8'", 
               "'KZJAJ0'", "'W8BH47'", "'CP8RPW9'", "'52TD5'", "'TLKV4'")

sqlQuery(myConn, paste("SELECT TOP 10000 [AxiomaDate]
                      ,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
                       FROM [PortfolioAnalytics].[Data_Axioma]
                       Where  AxiomaID IN (",paste(UKWinnersID, collapse = ","),")"))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download