· 8 years ago · Jan 08, 2018, 07:52 PM
1SalvaTabela=function(dado=null,tabela=NULL,anexar=T,msgs=F){ #tabela entre aspas
2 myconn <-odbcConnect("db", uid="user", pwd="user")
3 sqlSave(myconn, dado, tablename=tabela, append=anexar, rownames=F, colnames=F,
4 verbose=msgs ,safer=F,addPK = F,fast = T,test = F,nastring = NULL)
5 odbcClose(myconn)
6}
7
8SET ANSI_NULLS ON
9GO
10SET QUOTED_IDENTIFIER ON
11GO
12SET ANSI_PADDING ON
13GO
14CREATE TABLE [dbo].[VOLATILIDADE](
15 [nu_pregao] [smallint] NOT NULL,
16 [cd_papel] [varchar](12) NOT NULL,
17 [dp_1mes] [float] NULL,
18 [dp_1mes_ano] [float] NULL,
19 [dp_3mes] [float] NULL,
20 [dp_3mes_ano] [float] NULL,
21 [dp_6mes] [float] NULL,
22 [dp_6mes_ano] [float] NULL,
23 [dp_1ano] [float] NULL,
24 [dp_1ano_ano] [float] NULL
25) ON [PRIMARY]
26GO
27SET ANSI_PADDING OFF
28GO
29
30dput(vola[1:10,])
31structure(list(nu_pregao = c(1736L, 1736L, 1736L, 1736L, 1736L,
321736L, 1736L, 1736L, 1736L, 1736L), cd_papel = c("IBOV", "AALL34",
33"AALR3", "AAPL34", "ABBV34", "ABCB4", "ABEV3", "ABTT34", "ACNB34",
34"ADHM3"), dp_1mes = c(0.0093541434669, 0.0226610028876, 0.0158482263607,
350.0118589873497, 0.0219992221103, 0.0137756719237, 0.0093549485001,
360.0061224012013, 0.0251298725228, 0.0194694996518), dp_1mes_ano = c(14.85,
3735.97, 25.16, 18.83, 34.92, 21.87, 14.85, 9.72, 39.89, 30.91),
38 dp_3mes = c(0.0110770032048, 0.028651703104, 0.0128418591687,
39 0.0156624551677, 0.0220279344654, 0.0147149305608, 0.0088953466459,
40 0.0173340920059, 0.0513817608954, 0.0285763082731), dp_3mes_ano = c(17.58,
41 45.48, 20.39, 24.86, 34.97, 23.36, 14.12, 27.52, 81.57, 45.36
42 ), dp_6mes = c(0.0098285299002, 0.0386031910559, 0.014081211417,
43 0.0155290586846, 0.0343188714876, 0.0148796968397, 0.0091674326793,
44 0.0414216799478, 0.050567461855, 0.0304403837816), dp_6mes_ano = c(15.6,
45 61.28, 22.35, 24.65, 54.48, 23.62, 14.55, 65.75, 80.27, 48.32
46 ), dp_1ano = c(0.0118025420991, 0.0523308371799, 0.0181646141503,
47 0.0164562202577, 0.0401053196889, 0.020080673238, 0.0106847820684,
48 0.0317086519374, 0.0416184054552, 0.0468330568235), dp_1ano_ano = c(18.74,
49 83.07, 28.84, 26.12, 63.67, 31.88, 16.96, 50.34, 66.07, 74.35
50 )), .Names = c("nu_pregao", "cd_papel", "dp_1mes", "dp_1mes_ano",
51"dp_3mes", "dp_3mes_ano", "dp_6mes", "dp_6mes_ano", "dp_1ano",
52"dp_1ano_ano"), row.names = c(NA, 10L), class = "data.frame")
53
54SalvaTabela(dado=vola,tabela='VOLATILIDADE',anexar=T,msgs=F)
55
56IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VOLATILIDADE]') AND type in (N'U'))
57DROP TABLE [dbo].[VOLATILIDADE]
58GO
59SET ANSI_NULLS ON
60GO
61SET QUOTED_IDENTIFIER ON
62GO
63SET ANSI_PADDING ON
64GO
65CREATE TABLE [dbo].[VOLATILIDADE](
66 [nu_pregao] [smallint] NOT NULL,
67 [cd_papel] [varchar](12) NOT NULL,
68 [dp_1mes] [float] NULL,
69 [dp_1mes_ano] [float] NULL,
70 [dp_3mes] [float] NULL,
71 [dp_3mes_ano] [float] NULL,
72 [dp_6mes] [float] NULL,
73 [dp_6mes_ano] [float] NULL,
74 [dp_1ano] [float] NULL,
75 [dp_1ano_ano] [float] NULL,
76 CONSTRAINT [PK_VOLATILIDADE] PRIMARY KEY CLUSTERED
77(
78 [nu_pregao] ASC,
79 [cd_papel] ASC
80)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
81) ON [PRIMARY]
82GO
83SET ANSI_PADDING OFF
84GO
85
86SalvaTabela(dado=vola,tabela='VOLATILIDADE',anexar=T,msgs=F)
87 Show Traceback
88
89 Rerun with Debug
90 Error in sqlSave(myconn, dado, tablename = tabela, append = anexar, rownames = F, :
91 42S02 3701 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table 'VOLATILIDADE', because it does not exist or you do not have permission.
92[RODBC] ERROR: Could not SQLExecDirect 'DROP TABLE "VOLATILIDADE"'