· 8 years ago · Jan 07, 2018, 12:40 AM
1IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimZona')
2 CREATE TABLE [dbo].[DimZona](
3 [ZonaKey] [int] NOT NULL,
4 [Codigo] [numeric](5, 0) NOT NULL,
5 [Zona] [int] NOT NULL,
6 PRIMARY KEY CLUSTERED
7 (
8 [ZonaKey] 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]
11ELSE
12 TRUNCATE TABLE DimZona
13
14IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimVendedor')
15 CREATE TABLE [dbo].[DimVendedor](
16 [VendedorKey] [int] NOT NULL,
17 [Empresa] [varchar](10) NOT NULL,
18 [Vendedor] [varchar](20) NOT NULL,
19 [Codigo] [numeric](4, 0) NOT NULL,
20 [Nome] [varchar](40) NOT NULL,
21 [Morada] [varchar](40) NOT NULL,
22 [CodigoPostal] [varchar](28) NOT NULL,
23 [Lugar] [varchar](43) NOT NULL,
24 [Telefone] [varchar](60) NOT NULL,
25 [Email] [varchar](45) NOT NULL,
26 [DataCriacao] [datetime] NOT NULL,
27 [DataInicio] [datetime] NOT NULL,
28 [DataFim] [datetime] NOT NULL,
29 [Ativo] [bit] NOT NULL,
30 PRIMARY KEY CLUSTERED
31 (
32 [VendedorKey] ASC
33 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
34 ) ON [PRIMARY]
35ELSE
36 TRUNCATE TABLE DimVendedor
37
38IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimProduto')
39 CREATE TABLE [dbo].[DimProduto](
40 [ProdutoKey] [int] NOT NULL,
41 [Referencia] [char](18) NOT NULL,
42 [Designacao] [char](60) NOT NULL,
43 [Familia] [char](60) NOT NULL,
44 [Stock] [numeric](13, 3) NOT NULL,
45 [PrecoVenda] [numeric](19, 6) NOT NULL,
46 [PontoEncomenda] [numeric](10, 3) NOT NULL,
47 [StockMinimo] [numeric](13, 3) NOT NULL,
48 [DataAbertura] [datetime] NOT NULL,
49 [Categoria] [int] NOT NULL,
50 [Classificacao] [int] NOT NULL,
51 [PrecoCusto] [int] NOT NULL,
52 [QuantidadeCativa] [int] NOT NULL,
53 [TaxaIVA] [int] NOT NULL,
54 [DataCriacao] [datetime] NOT NULL,
55 [DataInicio] [datetime] NOT NULL,
56 [DataFim] [datetime] NOT NULL,
57 [Ativo] [bit] NOT NULL,
58 PRIMARY KEY CLUSTERED
59 (
60 [ProdutoKey] ASC
61 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
62 ) ON [PRIMARY]
63ELSE
64 TRUNCATE TABLE DimProduto
65
66IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimMoeda')
67 CREATE TABLE [dbo].[DimMoeda](
68 [MoedaKey] [int] NOT NULL,
69 [MoedaId] [int] NOT NULL,
70 [Abeviatura] [varchar](3) NOT NULL,
71 [Descritivo] [varchar](20) NOT NULL,
72 PRIMARY KEY CLUSTERED
73 (
74 [MoedaKey] ASC
75 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
76 ) ON [PRIMARY]
77ELSE
78 TRUNCATE TABLE DimMoeda
79
80IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimFuncionario')
81 CREATE TABLE [dbo].[DimFuncionario](
82 [FuncionarioKey] [int] NOT NULL,
83 [Empresa] [varchar](10) NOT NULL,
84 [Numero] [numeric](6, 0) NOT NULL,
85 [Iniciais] [varchar](3) NOT NULL,
86 [Codigo] [varchar](20) NOT NULL,
87 [NomeProprio] [varchar](30) NOT NULL,
88 [Grupo] [varchar](20) NOT NULL,
89 [Departamento] [varchar](20) NOT NULL,
90 [Email] [varchar](100) NOT NULL,
91 [Chefe] [int] NOT NULL,
92 [DataCriacao] [datetime] NOT NULL,
93 [DataInicio] [datetime] NOT NULL,
94 [DateFim] [datetime] NOT NULL,
95 [Ativo] [bit] NOT NULL,
96 PRIMARY KEY CLUSTERED
97 (
98 [FuncionarioKey] ASC
99 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
100 ) ON [PRIMARY]
101ELSE
102 TRUNCATE TABLE DimFuncionario
103
104IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimFornecedor')
105 CREATE TABLE [dbo].[DimFornecedor](
106 [FornecedorKey] [int] NOT NULL,
107 [Empresa] [varchar](10) NOT NULL,
108 [Numero] [numeric](10, 0) NOT NULL,
109 [Nome] [varchar](55) NOT NULL,
110 [Fax] [varchar](60) NOT NULL,
111 [Telefone] [varchar](60) NOT NULL,
112 [Contacto] [varchar](30) NOT NULL,
113 [Morada] [varchar](55) NOT NULL,
114 [CodigoPostal] [varchar](45) NOT NULL,
115 [Localidade] [varchar](33) NOT NULL,
116 [NumeroContribuinte] [varchar](20) NOT NULL,
117 [Zona] [varchar](50) NOT NULL,
118 [Tipo] [varchar](20) NOT NULL,
119 [Desconto] [numeric](5, 2) NOT NULL,
120 [Vencimento] [numeric](3, 0) NOT NULL,
121 [FuncaoContrato] [varchar](15) NOT NULL,
122 [Email] [varchar](45) NOT NULL,
123 [Nacionalidade] [varchar](20) NOT NULL,
124 [Telemovel] [varchar](45) NOT NULL,
125 [Saldo] [numeric](19, 6) NOT NULL,
126 [Plafond] [numeric](19, 6) NOT NULL,
127 [Classe] [varchar](30) NOT NULL,
128 [DataCriacao] [datetime] NOT NULL,
129 [DataInicio] [datetime] NOT NULL,
130 [DataFim] [datetime] NOT NULL,
131 [Ativo] [bit] NOT NULL,
132 PRIMARY KEY CLUSTERED
133 (
134 [FornecedorKey] ASC
135 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
136 ) ON [PRIMARY]
137ELSE
138 TRUNCATE TABLE DimFornecedor
139
140IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimEmpresa')
141 CREATE TABLE [dbo].[DimEmpresa](
142 [EmpresaKey] [int] NOT NULL,
143 [Nome] [varchar](55) NOT NULL,
144 [Numero] [numeric](10, 0) NOT NULL,
145 [NumeroContribuinte] [varchar](20) NOT NULL,
146 [Morada] [varchar](50) NOT NULL,
147 [Localidade] [varchar](43) NOT NULL,
148 [CodigoPostal] [varchar](45) NOT NULL,
149 [Telefone] [varchar](60) NOT NULL,
150 [Fax] [varchar](60) NOT NULL,
151 [Contacto] [varchar](40) NOT NULL,
152 [Profissao] [varchar](20) NOT NULL,
153 [Email] [varchar](45) NOT NULL,
154 [Telemovel] [varchar](45) NOT NULL,
155 [DataInicio] [datetime] NOT NULL,
156 [DataFim] [datetime] NOT NULL,
157 [Ativo] [bit] NOT NULL,
158 PRIMARY KEY CLUSTERED
159 (
160 [EmpresaKey] ASC
161 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
162 ) ON [PRIMARY]
163ELSE
164 TRUNCATE TABLE DimEmpresa
165
166IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimDate')
167 CREATE TABLE [dbo].[DimDate](
168 [DateKey] [int] NOT NULL,
169 [FullDate] [datetime] NOT NULL,
170 [Year] [numeric](10, 0) NOT NULL,
171 [Semester] [tinyint] NOT NULL,
172 [Quarter] [tinyint] NOT NULL,
173 [Month] [tinyint] NOT NULL,
174 [MonthName] [varchar](10) NOT NULL,
175 [Week] [tinyint] NOT NULL,
176 [DayNumberOfYear] [tinyint] NOT NULL,
177 [DayNumberOfMonth] [tinyint] NOT NULL,
178 [DayNumberOfWeek] [tinyint] NOT NULL,
179 [DayOfWeek] [varchar](10) NOT NULL,
180 [Weekend] [varchar](3) NOT NULL,
181 PRIMARY KEY CLUSTERED
182 (
183 [DateKey] ASC
184 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
185 ) ON [PRIMARY]
186ELSE
187 TRUNCATE TABLE DimDate
188
189IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimCliente')
190 CREATE TABLE [dbo].[DimCliente](
191 [ClienteKey] [int] NOT NULL,
192 [Empresa] [varchar](10) NOT NULL,
193 [Nome] [varchar](55) NOT NULL,
194 [Numero] [numeric](10, 0) NOT NULL,
195 [NumeroContribute] [varchar](20) NOT NULL,
196 [Saldo] [numeric](19, 6) NOT NULL,
197 [Fax] [varchar](60) NOT NULL,
198 [Telefone] [int] NOT NULL,
199 [Contacto] [varchar](30) NOT NULL,
200 [Morada] [varchar](55) NOT NULL,
201 [Lugar] [varchar](43) NOT NULL,
202 [CodigoPostal] [varchar](45) NOT NULL,
203 [Localidade] [varchar](33) NOT NULL,
204 [Zona] [varchar](50) NOT NULL,
205 [Tipo] [varchar](20) NOT NULL,
206 [Vencimento] [numeric](3, 0) NOT NULL,
207 [Plafond] [numeric](19, 6) NOT NULL,
208 [NIB] [varchar](28) NOT NULL,
209 [Segmento] [varchar](25) NOT NULL,
210 [Email] [varchar](45) NOT NULL,
211 [CentroCusto] [varchar](20) NOT NULL,
212 [Telemovel] [varchar](45) NOT NULL,
213 [DataCriacao] [datetime] NOT NULL,
214 [DataInicio] [datetime] NOT NULL,
215 [DataFim] [datetime] NOT NULL,
216 [Ativo] [bit] NOT NULL,
217 PRIMARY KEY CLUSTERED
218 (
219 [ClienteKey] ASC
220 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
221 ) ON [PRIMARY]
222ELSE
223 TRUNCATE TABLE DimCliente
224
225IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'DimArmazem')
226 CREATE TABLE [dbo].[DimArmazem](
227 [ArmazemKey] [int] NOT NULL,
228 [Codigo] [int] NOT NULL,
229 [Armazem] [varchar](50) NOT NULL,
230 PRIMARY KEY CLUSTERED
231 (
232 [ArmazemKey] ASC
233 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
234 ) ON [PRIMARY]
235ELSE
236 TRUNCATE TABLE DimArmazem