USE MainDataBase

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempDescriptions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempDescriptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempRoster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempRoster]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempDocuments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempDocuments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempConsignees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempConsignees]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempConsignors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempConsignors]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempInvoices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempInvoices]
GO

CREATE TABLE [dbo].[tempConsignees] (
	[idSender] [smallint] NOT NULL ,
	[compilingYear] [smallint] NOT NULL ,
	[number] [bigint] NOT NULL ,
	[idPredpr] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempConsignors] (
	[idSender] [smallint] NOT NULL ,
	[compilingYear] [smallint] NOT NULL ,
	[number] [bigint] NOT NULL ,
	[idPredpr] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempDescriptions] (
	[idDesc] [tinyint] NOT NULL ,
	[idStr] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempDocuments] (
	[idSender] [smallint] NOT NULL ,
	[compilingYear] [smallint] NOT NULL ,
	[number] [bigint] NOT NULL ,
	[code] [int] NOT NULL ,
	[docDate] [datetime] NOT NULL ,
	[blankCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[seria] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[docNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
	[vidDoc] [tinyint] NOT NULL,
	[KodSkl] [int] NULL,
	[idPredpr] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempInvoices] (
	[idSender] [smallint] NOT NULL ,
	[compilingYear] [smallint] NOT NULL ,
	[number] [bigint] NOT NULL ,
	[invoice] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[dateCancelled] [datetime] NULL ,
	[documentType] [tinyint] NOT NULL ,
	[dateTransaction] [datetime] NULL ,
	[idProvider] [int] NULL ,
	[providerStatus] [tinyint] NULL ,
	[specialDealGoods] [bit] NULL ,
	[principalId] [smallint] NULL ,
	[principalYear] [smallint] NULL ,
	[principalNumber] [bigint] NULL ,
	[principalDate] [datetime] NULL ,
	[vendorId] [smallint] NULL ,
	[vendorYear] [smallint] NULL ,
	[vendorNumber] [bigint] NULL ,
	[vendorDate] [datetime] NULL ,
	[pDeclaration] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[dateRelease] [datetime] NULL ,
	[dateActualExport] [datetime] NULL ,
	[pTaxesNumber] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[pTaxesDate] [datetime] NULL ,
	[idRecipient] [int] NULL ,
	[recipientStatus] [tinyint] NULL ,
	[rDeclaration] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[rTaxesNumber] [varchar] (25) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[rTaxesDate] [datetime] NULL ,
	[dateImport] [datetime] NULL ,
	[contractNumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[contractDate] [datetime] NULL ,
	[description] [varchar] (512) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[totalCostVat] [decimal](18, 3) NOT NULL ,
	[totalExcise] [decimal](18, 3) NOT NULL ,
	[totalVat] [decimal](18, 3) NOT NULL ,
	[totalCost] [decimal](18, 3) NOT NULL ,
	[completed] [bit] NULL ,
	[Sv] [bit] NOT NULL DEFAULT(0)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempRoster] (
	[idSender] [smallint] NOT NULL ,
	[compilingYear] [smallint] NOT NULL ,
	[number] [bigint] NOT NULL ,
	[pnt] [int] NULL ,
	[idStr] [int] NOT NULL ,
	[itemNumber] [int] NULL ,
	[name] [varchar] (512) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
	[code] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[code_oced] [varchar] (5) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[units] [smallint] NULL ,
	[count] [decimal](18, 4) NULL ,
	[price] [decimal](18, 3) NULL ,
	[cost] [decimal](18, 3) NOT NULL ,
	[summaExcise] [decimal](18, 3) NULL ,
	[rate] [decimal](5, 3) NULL ,
	[idRateType] [tinyint] NULL ,
	[summaVat] [decimal](18, 3) NOT NULL ,
	[costVat] [decimal](18, 3) NOT NULL 
) ON [PRIMARY]
GO


INSERT INTO tempInvoices
SELECT * FROM invoices

INSERT INTO tempDocuments
SELECT  documents.idSender, documents.compilingYear, documents.number, documents.code, documents.docDate, documents.blankCode, documents.seria,documents.docNumber, documents.VidDoc,
(SELECT TOP 1 idPredpr FROM consignors WHERE consignors.idSender = documents.idSender AND consignors.compilingYear = documents.compilingYear AND consignors.number = documents.number) as KodSkl,
(SELECT TOP 1 idPredpr FROM consignees WHERE consignees.idSender = documents.idSender AND consignees.compilingYear = documents.compilingYear AND consignees.number = documents.number) as idPredpr
FROM documents

INSERT INTO tempConsignees
SELECT * FROM consignees

INSERT INTO tempConsignors
SELECT * FROM consignors

INSERT INTO tempDescriptions
SELECT * FROM descriptions

INSERT INTO tempRoster
SELECT *
FROM roster