I've read through previous questions/answers concerning this issue but none seemed to answer my problem. My end goal is to get data from R into a SQL table.
In trying to do so, I first created the table [PL_DEV].[PL_A].[X] in Microsoft SQL Server 2012 with the following columns: ST VARCHAR(10), YEAR VARCHAR(10), Intercept FLOAT, Y FLOAT, Z FLOAT, U FLOAT. A primary key was added to the ST, YEAR columns.
From there, I established an RODBC connection which I am calling conn.
After setting that up, I created a data-frame called df that looks like this for the first row of data:
ST YEAR INTERCEPT Y Z U
"01" "2009" -50012.37 0.6341358 16569.52 61.42544
Once this was set, I attempted to insert data from this data-frame (df) into the SQL table I had created ([PL_DEV].[PL_A].[X]). I attempted to do that with the following statement:
sqlSave(conn, dat = df, tablename = "[PL_DEV].[PL_A].[X]", append = TRUE, rownames = FALSE)
I set append = TRUE so that it wouldn't create the table but rather insert data and also set rownames = FALSE so it wouldn't add an extra/unnecessary column.
However, when I execute the statement above, I get the following error:
Error in sqlSave(conn, df, tablename = "[PL_DEV].[PL_A].[X]", : 42S01 2714 [Microsoft][SQL Server Native Client 11.0][SQL Server] There is already an object named 'X' in the database. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [PL_DEV].[PL_A].[X]
("ST" varchar(255), "YEAR" varchar(255), "INTERCEPT" float, "Y" float, "Z" float, "U" float)'
I can get data to insert into a SQL table ([master].[dbo].[df] to be more specific) if I execute the following:
sqlSave(conn, dat = df)
However, once data is in this table, the same issue arises and I cannot insert more data into the table.
Does anyone know what I'm doing wrong or another function that will do the same thing for me? I just started programming in R this past week. Any insight would be greatly appreciated.
Figured out a work around.
For the first iteration of my loop, I used the following command:
sqlSave(conn, dat = df, rownames = FALSE)
For iterations 2 through n of my loop, I used the following command:
sqlSave(conn, dat = df, append = TRUE, rownames = FALSE)
This created a table [dbo].[df] in the database [PL_DEV] for me (since I set up my RODBC directly to that database).