· 8 years ago · Jan 06, 2018, 11:22 PM
1IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimArmazem')
2 CREATE TABLE [dbo].[DimArmazem](
3 [ArmazemKey] [int] NOT NULL,
4 [Codigo] [int] NOT NULL,
5 [Armazem] [varchar](50) NOT NULL,
6 PRIMARY KEY CLUSTERED
7 (
8 [ArmazemKey] ASC
9 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
10 ) ON [PRIMARY]
11 GO
12ELSE
13 TRUNCATE TABLE DimArmazem
14
15IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimZona')
16 CREATE TABLE [dbo].[DimZona](
17 [ZonaKey] [int] NOT NULL,
18 [Codigo] [numeric](5, 0) NOT NULL,
19 [Zona] [int] NOT NULL,
20 PRIMARY KEY CLUSTERED
21 (
22 [ZonaKey] ASC
23 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
24 ) ON [PRIMARY]
25ELSE
26 TRUNCATE TABLE DimZona
27
28IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimVendedor')
29 CREATE TABLE [dbo].[DimVendedor](
30 [VendedorKey] [int] NOT NULL,
31 [Empresa] [varchar](10) NOT NULL,
32 [Vendedor] [varchar](20) NOT NULL,
33 [Codigo] [numeric](4, 0) NOT NULL,
34 [Nome] [varchar](40) NOT NULL,
35 [Morada] [varchar](40) NOT NULL,
36 [CodigoPostal] [varchar](28) NOT NULL,
37 [Lugar] [varchar](43) NOT NULL,
38 [Telefone] [varchar](60) NOT NULL,
39 [Email] [varchar](45) NOT NULL,
40 [DataCriacao] [int] NOT NULL,
41 [DataInicio] [int] NOT NULL,
42 [DataFim] [int] NOT NULL,
43 [Ativo] [bit] NOT NULL,
44 PRIMARY KEY CLUSTERED
45 (
46 [VendedorKey] ASC
47 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
48 ) ON [PRIMARY]
49
50 ALTER TABLE [dbo].[DimVendedor] WITH CHECK ADD CONSTRAINT [FKDimVendedo246184] FOREIGN KEY([DataFim])
51 REFERENCES [dbo].[DimDate] ([DateKey])
52
53 ALTER TABLE [dbo].[DimVendedor] CHECK CONSTRAINT [FKDimVendedo246184]
54
55 ALTER TABLE [dbo].[DimVendedor] WITH CHECK ADD CONSTRAINT [FKDimVendedo722170] FOREIGN KEY([DataInicio])
56 REFERENCES [dbo].[DimDate] ([DateKey])
57
58 ALTER TABLE [dbo].[DimVendedor] CHECK CONSTRAINT [FKDimVendedo722170]
59
60 ALTER TABLE [dbo].[DimVendedor] WITH CHECK ADD CONSTRAINT [FKDimVendedo793117] FOREIGN KEY([DataCriacao])
61 REFERENCES [dbo].[DimDate] ([DateKey])
62
63 ALTER TABLE [dbo].[DimVendedor] CHECK CONSTRAINT [FKDimVendedo793117]
64ELSE
65 TRUNCATE TABLE DimVendedor
66
67IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimProduto')
68 CREATE TABLE [dbo].[DimProduto](
69 [ProdutoKey] [int] NOT NULL,
70 [Referencia] [char](18) NOT NULL,
71 [Designacao] [char](60) NOT NULL,
72 [Familia] [char](60) NOT NULL,
73 [Stock] [numeric](13, 3) NOT NULL,
74 [PrecoVenda] [numeric](19, 6) NOT NULL,
75 [PontoEncomenda] [numeric](10, 3) NOT NULL,
76 [StockMinimo] [numeric](13, 3) NOT NULL,
77 [DataAbertura] [int] NOT NULL,
78 [Categoria] [int] NOT NULL,
79 [Classificacao] [int] NOT NULL,
80 [PrecoCusto] [int] NOT NULL,
81 [QuantidadeCativa] [int] NOT NULL,
82 [TaxaIVA] [int] NOT NULL,
83 [DataCriacao] [int] NOT NULL,
84 [DataInicio] [int] NOT NULL,
85 [DataFim] [int] NOT NULL,
86 [Ativo] [bit] NOT NULL,
87 PRIMARY KEY CLUSTERED
88 (
89 [ProdutoKey] ASC
90 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
91 ) ON [PRIMARY]
92
93 ALTER TABLE [dbo].[DimProduto] WITH CHECK ADD CONSTRAINT [FKDimProduto167192] FOREIGN KEY([DataFim])
94 REFERENCES [dbo].[DimDate] ([DateKey])
95
96 ALTER TABLE [dbo].[DimProduto] CHECK CONSTRAINT [FKDimProduto167192]
97
98 ALTER TABLE [dbo].[DimProduto] WITH CHECK ADD CONSTRAINT [FKDimProduto223106] FOREIGN KEY([DataAbertura])
99 REFERENCES [dbo].[DimDate] ([DateKey])
100
101 ALTER TABLE [dbo].[DimProduto] CHECK CONSTRAINT [FKDimProduto223106]
102
103 ALTER TABLE [dbo].[DimProduto] WITH CHECK ADD CONSTRAINT [FKDimProduto234903] FOREIGN KEY([DataCriacao])
104 REFERENCES [dbo].[DimDate] ([DateKey])
105
106 ALTER TABLE [dbo].[DimProduto] CHECK CONSTRAINT [FKDimProduto234903]
107
108 ALTER TABLE [dbo].[DimProduto] WITH CHECK ADD CONSTRAINT [FKDimProduto691205] FOREIGN KEY([DataInicio])
109 REFERENCES [dbo].[DimDate] ([DateKey])
110
111 ALTER TABLE [dbo].[DimProduto] CHECK CONSTRAINT [FKDimProduto691205]
112ELSE
113 TRUNCATE TABLE DimProduto
114
115IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimMoeda')
116 CREATE TABLE [dbo].[DimMoeda](
117 [MoedaKey] [int] NOT NULL,
118 [MoedaId] [int] NOT NULL,
119 [Abeviatura] [int] NOT NULL,
120 [Descritivo] [int] NOT NULL,
121 PRIMARY KEY CLUSTERED
122 (
123 [MoedaKey] ASC
124 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
125 ) ON [PRIMARY]
126ELSE
127 TRUNCATE TABLE DimMoeda
128
129IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimFuncionario')
130 CREATE TABLE [dbo].[DimFuncionario](
131 [FuncionarioKey] [int] NOT NULL,
132 [Empresa] [varchar](10) NOT NULL,
133 [Numero] [numeric](6, 0) NOT NULL,
134 [Iniciais] [varchar](3) NOT NULL,
135 [Codigo] [varchar](20) NOT NULL,
136 [NomeProprio] [varchar](30) NOT NULL,
137 [Grupo] [varchar](20) NOT NULL,
138 [Departamento] [varchar](20) NOT NULL,
139 [Email] [varchar](100) NOT NULL,
140 [Chefe] [int] NOT NULL,
141 [DataCriacao] [int] NOT NULL,
142 [DataInicio] [int] NOT NULL,
143 [DateFim] [int] NOT NULL,
144 [Ativo] [bit] NOT NULL,
145 PRIMARY KEY CLUSTERED
146 (
147 [FuncionarioKey] ASC
148 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
149 ) ON [PRIMARY]
150
151 ALTER TABLE [dbo].[DimFuncionario] WITH CHECK ADD CONSTRAINT [FKDimFuncion115513] FOREIGN KEY([DateFim])
152 REFERENCES [dbo].[DimDate] ([DateKey])
153
154 ALTER TABLE [dbo].[DimFuncionario] CHECK CONSTRAINT [FKDimFuncion115513]
155
156 ALTER TABLE [dbo].[DimFuncionario] WITH CHECK ADD CONSTRAINT [FKDimFuncion303594] FOREIGN KEY([Chefe])
157 REFERENCES [dbo].[DimFuncionario] ([FuncionarioKey])
158
159 ALTER TABLE [dbo].[DimFuncionario] CHECK CONSTRAINT [FKDimFuncion303594]
160
161 ALTER TABLE [dbo].[DimFuncionario] WITH CHECK ADD CONSTRAINT [FKDimFuncion479637] FOREIGN KEY([DataInicio])
162 REFERENCES [dbo].[DimDate] ([DateKey])
163
164 ALTER TABLE [dbo].[DimFuncionario] CHECK CONSTRAINT [FKDimFuncion479637]
165
166 ALTER TABLE [dbo].[DimFuncionario] WITH CHECK ADD CONSTRAINT [FKDimFuncion935939] FOREIGN KEY([DataCriacao])
167 REFERENCES [dbo].[DimDate] ([DateKey])
168
169 ALTER TABLE [dbo].[DimFuncionario] CHECK CONSTRAINT [FKDimFuncion935939]
170ELSE
171 TRUNCATE TABLE DimFuncionario
172
173IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimFornecedor')
174 CREATE TABLE [dbo].[DimFornecedor](
175 [FornecedorKey] [int] NOT NULL,
176 [Empresa] [varchar](10) NOT NULL,
177 [Numero] [numeric](10, 0) NOT NULL,
178 [Nome] [varchar](55) NOT NULL,
179 [Fax] [varchar](60) NOT NULL,
180 [Telefone] [varchar](60) NOT NULL,
181 [Contacto] [varchar](30) NOT NULL,
182 [Morada] [varchar](55) NOT NULL,
183 [CodigoPostal] [varchar](45) NOT NULL,
184 [Localidade] [varchar](33) NOT NULL,
185 [NumeroContribuinte] [varchar](20) NOT NULL,
186 [Zona] [varchar](50) NOT NULL,
187 [Tipo] [varchar](20) NOT NULL,
188 [Desconto] [numeric](5, 2) NOT NULL,
189 [Vencimento] [numeric](3, 0) NOT NULL,
190 [FuncaoContrato] [varchar](15) NOT NULL,
191 [Email] [varchar](45) NOT NULL,
192 [Nacionalidade] [varchar](20) NOT NULL,
193 [Telemovel] [varchar](45) NOT NULL,
194 [Saldo] [numeric](19, 6) NOT NULL,
195 [Plafond] [numeric](19, 6) NOT NULL,
196 [Classe] [varchar](30) NOT NULL,
197 [DataCriacao] [int] NOT NULL,
198 [DataInicio] [int] NOT NULL,
199 [DataFim] [int] NOT NULL,
200 [Ativo] [bit] NOT NULL,
201 PRIMARY KEY CLUSTERED
202 (
203 [FornecedorKey] ASC
204 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
205 ) ON [PRIMARY]
206
207 ALTER TABLE [dbo].[DimFornecedor] WITH CHECK ADD CONSTRAINT [FKDimFornece432833] FOREIGN KEY([DataFim])
208 REFERENCES [dbo].[DimDate] ([DateKey])
209
210 ALTER TABLE [dbo].[DimFornecedor] CHECK CONSTRAINT [FKDimFornece432833]
211
212 ALTER TABLE [dbo].[DimFornecedor] WITH CHECK ADD CONSTRAINT [FKDimFornece606468] FOREIGN KEY([DataCriacao])
213 REFERENCES [dbo].[DimDate] ([DateKey])
214
215 ALTER TABLE [dbo].[DimFornecedor] CHECK CONSTRAINT [FKDimFornece606468]
216
217 ALTER TABLE [dbo].[DimFornecedor] WITH CHECK ADD CONSTRAINT [FKDimFornece908819] FOREIGN KEY([DataInicio])
218 REFERENCES [dbo].[DimDate] ([DateKey])
219
220 ALTER TABLE [dbo].[DimFornecedor] CHECK CONSTRAINT [FKDimFornece908819]
221ELSE
222 TRUNCATE TABLE DimFornecedor
223
224IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimEmpresa')
225 CREATE TABLE [dbo].[DimEmpresa](
226 [EmpresaKey] [int] NOT NULL,
227 [Nome] [varchar](55) NOT NULL,
228 [Numero] [numeric](10, 0) NOT NULL,
229 [NumeroContribuinte] [varchar](20) NOT NULL,
230 [Morada] [varchar](50) NOT NULL,
231 [Localidade] [varchar](43) NOT NULL,
232 [CodigoPostal] [varchar](45) NOT NULL,
233 [Telefone] [varchar](60) NOT NULL,
234 [Fax] [varchar](60) NOT NULL,
235 [Contacto] [varchar](40) NOT NULL,
236 [Profissao] [varchar](20) NOT NULL,
237 [Email] [varchar](45) NOT NULL,
238 [Telemovel] [varchar](45) NOT NULL,
239 [DataInicio] [int] NOT NULL,
240 [DataFim] [int] NOT NULL,
241 [Ativo] [bit] NOT NULL,
242 PRIMARY KEY CLUSTERED
243 (
244 [EmpresaKey] ASC
245 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
246 ) ON [PRIMARY]
247
248 ALTER TABLE [dbo].[DimEmpresa] WITH CHECK ADD CONSTRAINT [FKDimEmpresa263411] FOREIGN KEY([DataInicio])
249 REFERENCES [dbo].[DimDate] ([DateKey])
250
251 ALTER TABLE [dbo].[DimEmpresa] CHECK CONSTRAINT [FKDimEmpresa263411]
252
253 ALTER TABLE [dbo].[DimEmpresa] WITH CHECK ADD CONSTRAINT [FKDimEmpresa739397] FOREIGN KEY([DataFim])
254 REFERENCES [dbo].[DimDate] ([DateKey])
255
256 ALTER TABLE [dbo].[DimEmpresa] CHECK CONSTRAINT [FKDimEmpresa739397]
257ELSE
258 TRUNCATE TABLE DimEmpresa
259
260IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimDate')
261 CREATE TABLE [dbo].[DimDate](
262 [DateKey] [int] NOT NULL,
263 [FullDate] [datetime] NOT NULL,
264 [Year] [numeric](10, 0) NOT NULL,
265 [Semester] [tinyint] NOT NULL,
266 [Quarter] [tinyint] NOT NULL,
267 [Month] [tinyint] NOT NULL,
268 [MonthName] [varchar](10) NOT NULL,
269 [Week] [tinyint] NOT NULL,
270 [DayNumberOfYear] [tinyint] NOT NULL,
271 [DayNumberOfMonth] [tinyint] NOT NULL,
272 [DayNumberOfWeek] [tinyint] NOT NULL,
273 [DayOfWeek] [varchar](10) NOT NULL,
274 [Weekend] [varchar](3) NOT NULL,
275 PRIMARY KEY CLUSTERED
276 (
277 [DateKey] ASC
278 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
279 ) ON [PRIMARY]
280ELSE
281 TRUNCATE TABLE DimDate
282
283IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimCliente')
284 CREATE TABLE [dbo].[DimCliente](
285 [ClienteKey] [int] NOT NULL,
286 [Empresa] [varchar](10) NOT NULL,
287 [Nome] [varchar](55) NOT NULL,
288 [Numero] [numeric](10, 0) NOT NULL,
289 [NumeroContribute] [varchar](20) NOT NULL,
290 [Saldo] [numeric](19, 6) NOT NULL,
291 [Fax] [varchar](60) NOT NULL,
292 [Telefone] [int] NOT NULL,
293 [Contacto] [varchar](30) NOT NULL,
294 [Morada] [varchar](55) NOT NULL,
295 [Lugar] [varchar](43) NOT NULL,
296 [CodigoPostal] [varchar](45) NOT NULL,
297 [Localidade] [varchar](33) NOT NULL,
298 [Zona] [varchar](50) NOT NULL,
299 [Tipo] [varchar](20) NOT NULL,
300 [Vencimento] [numeric](3, 0) NOT NULL,
301 [Plafond] [numeric](19, 6) NOT NULL,
302 [NIB] [varchar](28) NOT NULL,
303 [Segmento] [varchar](25) NOT NULL,
304 [Email] [varchar](45) NOT NULL,
305 [CentroCusto] [varchar](20) NOT NULL,
306 [Telemovel] [varchar](45) NOT NULL,
307 [DataCriacao] [int] NOT NULL,
308 [DataInicio] [int] NOT NULL,
309 [DataFim] [int] NOT NULL,
310 [Ativo] [bit] NOT NULL,
311 PRIMARY KEY CLUSTERED
312 (
313 [ClienteKey] ASC
314 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
315 ) ON [PRIMARY]
316
317 ALTER TABLE [dbo].[DimCliente] WITH CHECK ADD CONSTRAINT [FKDimCliente218158] FOREIGN KEY([DataInicio])
318 REFERENCES [dbo].[DimDate] ([DateKey])
319
320 ALTER TABLE [dbo].[DimCliente] CHECK CONSTRAINT [FKDimCliente218158]
321
322 ALTER TABLE [dbo].[DimCliente] WITH CHECK ADD CONSTRAINT [FKDimCliente297130] FOREIGN KEY([DataCriacao])
323 REFERENCES [dbo].[DimDate] ([DateKey])
324
325 ALTER TABLE [dbo].[DimCliente] CHECK CONSTRAINT [FKDimCliente297130]
326
327 ALTER TABLE [dbo].[DimCliente] WITH CHECK ADD CONSTRAINT [FKDimCliente742171] FOREIGN KEY([DataFim])
328 REFERENCES [dbo].[DimDate] ([DateKey])
329
330 ALTER TABLE [dbo].[DimCliente] CHECK CONSTRAINT [FKDimCliente742171]
331ELSE
332 TRUNCATE TABLE DimCliente