pk0CE0XJ

1library(DBI)
2library(dplyr)
3library(lubridate)
4
5# --- Connect to the database via RSQLServer and odbc --------------------------
6db <- "your SQL database"
7server <- "your SQL server"
8conn <- dbConnect(RSQLServer::SQLServer(), server = server, database = db,
9                  properties = list(user = "", password = "",
10                                    useNTLMv2 = TRUE, domain = "")
11)
12
13conn2 <- dbConnect(odbc::odbc(), dsn = "")
14
15# --- Create the test table ----------------------------------------------------
16dplyr::db_drop_table(conn, "TestTable")
17
18if (!dbExistsTable(conn, "TestTable")) {
19  TestProcessStr <- "
20  CREATE TABLE TestTable(
21  Process_ID INT NOT NULL IDENTITY(1,1),
22  Start_Dt DATE NOT NULL,
23  End_Dt DATE DEFAULT '9999-12-31',
24  Comment VARCHAR(30),
25  PRIMARY KEY( Process_ID )
26  );"
27
28  dbExecute(conn, TestProcessStr)
29} else {
30  message("TestTable exists")
31}
32
33# --- Write to test table using different connections --------------------------
34
35rowadd <- data_frame(Start_Dt = Sys.Date(), End_Dt = Sys.Date() + months(3),
36                     Comment = "SQLServer, Date as Date")
37
38write_res <- dbWriteTable(conn, name = "TestTable", 
39                          value = rowadd, append = T)
40
41# Convert all dates to character
42rowadd <- rowadd %>% mutate_if(is.Date, as.character) %>%
43  mutate(Comment = "SQLServer, Date as Char")
44
45write_res <- dbWriteTable(conn, name = "TestTable", 
46                          value = rowadd, append = T)
47
48rowadd <- data_frame(Start_Dt = Sys.Date(), End_Dt = Sys.Date() + months(3)) %>%
49  mutate(Comment = "ODBC, Date as Date")
50
51write_res <- dbWriteTable(conn2, name = "TestTable", 
52                          value = rowadd, append = T)
53
54
55# Convert all dates to character
56rowadd <- rowadd %>% mutate_if(is.Date, as.character) %>%
57  mutate(Comment = "ODBC, Date as Character")
58
59write_res <- dbWriteTable(conn2, name = "TestTable", 
60                          value = rowadd, append = T)
61
62
63# View database status
64ttab <- dbReadTable(conn, "TestTable")
65ttab
66
67# --- Disconnect ---------------------------------------------------------------
68
69dbDisconnect(conn)
70dbDisconnect(conn2)
71	
72Process_ID   Start_Dt     End_Dt                 Comment
731          1 2018-01-14 2018-04-14 SQLServer, Date as Date
742          2 2018-01-15 2018-04-15 SQLServer, Date as Char
753          3 2018-01-15 2018-04-15      ODBC, Date as Date
764          4 2018-01-15 2018-04-15 ODBC, Date as Character