Pasting data to SQL Query

Question

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 ".

try using paste(UKWinnersID, collapse = ",") instead of print. — Benjamin
– Benjamin, Commented Oct 2, 2017 at 13:54
This gets me close but not quite there - please see updated question above. I essentially need to remove the last two characters for the query to work. — user8491385
– user8491385, Commented Oct 2, 2017 at 14:06

Benjamin · Accepted Answer · 2017-10-02 14:11:00Z

1

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 = ","),")"))

answered Oct 2, 2017 at 14:11

Benjamin

17.3k8 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8491385 Over a year ago

Now my output is closer but there is still one ' at the end outside the bracket as : sqlQuery etc Where AxiomaID IN ( '1WKX6','ULTY8','JNZX0','8J4D8','KZJAJ0','W8BH47','CP8RPW9','52TD5','TLKV4' )'

RAFAEL ANTONIO VENTURA · Accepted Answer · 2017-10-05 15:22:21Z

I recommended a solution to a similar question. see Pasting Values in SQL Query through R

You could try defining a character variable with the query value. Then you can call function InsertListInQuery passing query and vector as it arguments as follow:

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

query <- "SELECT TOP 10000 [AxiomaDate]
                      ,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
                      FROM [PortfolioAnalytics].[Data_Axioma]
                     Where  AxiomaID IN ()"

SQLQuery <- InsertListInQuery(query, UKWinnersID)

SQLQuery
[1] "SELECT TOP 10000 [AxiomaDate]\n,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]\nFROM [PortfolioAnalytics].[Data_Axioma]\nWhere  AxiomaID IN ( '1WKX6' , 'ULTY8' , 'JNZX0' , '8J4D8' , 'KZJAJ0' , 'W8BH47' , 'CP8RPW9' , '52TD5' ,'TLKV4')"

I hope it helps.

Collectives™ on Stack Overflow

Pasting data to SQL Query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related