R2dCcs7X

1IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('TMP'))
2DROP TABLE TMP
3
4CREATE TABLE TMP
5(
6	ID INTEGER IDENTITY(1,1),
7
8	CName VARCHAR(35)NOT NULL,
9	CSurname VARCHAR(35)NOT NULL,
10	CPhoneNumber VARCHAR(35)NOT NULL,
11	CEmail VARCHAR(35) NOT NULL,
12	CCreditCard VARCHAR(35) ,
13
14	OrderTime datetime,
15	OrderPrice int,
16	
17	AdName varchar(35),
18	AdPublicationDate datetime,
19	AdsTextPrice int,
20	AdsHashtagsPrice int,
21	AdsImagesPrice int,
22);
23
24IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('customers_'))
25CREATE TABLE customers_
26(
27	ID INTEGER primary key,
28
29	CName VARCHAR(35)NOT NULL,
30	CSurname VARCHAR(35)NOT NULL,
31	CPhoneNumber VARCHAR(35)NOT NULL,
32	CEmail VARCHAR(35) NOT NULL,
33	CCreditCard VARCHAR(35),
34);
35
36IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('orders_'))
37create table orders_(
38	ID INTEGER primary key,
39	OrderTime datetime,
40	OrderPrice int,
41	cust int 
42);
43IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('ads_'))
44create table ads_(
45	ID INTEGER primary key,
46	AdName varchar(35),
47	AdPublicationDate datetime,
48	AdsTextPrice int,
49	AdsHashtagsPrice int,
50	AdsImagesPrice int,
51	ord int 
52);
53
54INSERT INTO TMP 
55(CName,CSurname,CPhoneNumber, CEmail,CCreditCard,   
56  OrderTime,OrderPrice,   
57AdName, AdPublicationDate,AdsTextPrice,AdsHashtagsPrice, AdsImagesPrice)
58SELECT Name_, Surname, PhoneNumber,Email,CreditCardNumber, OrderTime, OrderPrice,
59AdName, AdPublicationDate,AdsTextPrice,AdsHashtagsPrice,AdsImagesPrice   FROM AdsList
60join OrderList on AdsList.OrderID = OrderList.OrderID
61join AdCustomer on AdCustomer.AdCustomerID = OrderList.AdCustomerID;
62
63
64declare @i int, @j int, @k int, @f int;
65declare @tName VARCHAR(35), @tSurname VARCHAR(35), @tPhoneNumber VARCHAR(35), @tEmail VARCHAR(35), @tCreditCard VARCHAR(35);
66declare @tOrdertime datetime, @tOrderPrice int;
67declare @tAdName varchar(35), @tAdPublication datetime, @tAdsTextPrice int, @tAdsHashtagsPrice int, @tAdsImagesPrice int;
68set @i = (select min(id) from tmp);
69
70while @i < (select max(id) from tmp)
71begin
72---------Customer---------
73	IF NOT EXISTS (SELECT * FROM customers)
74		SET @j = 1;
75	ELSE
76		SET @j = (SELECT MAX(ID) + 1 FROM customers);
77	
78	SET @tName = (SELECT CName FROM TMP WHERE ID = @i);
79	SET @tSurname = (SELECT CSurname FROM TMP WHERE ID = @i);
80	SET @tPhoneNumber = (SELECT CPhoneNumber FROM TMP WHERE ID = @i);
81	SET @tEmail = (SELECT CEmail FROM TMP WHERE ID = @i);
82	SET @tCreditCard = (SELECT CCreditCard FROM TMP WHERE ID = @i);
83
84	
85		IF NOT EXISTS(SELECT * FROM customers_ WHERE CName = @tName AND CSurname = @tSurname AND CPhoneNumber = @tPhoneNumber AND CEmail = @tEmail AND CPhoneNumber = @tPhoneNumber)
86		BEGIN
87			INSERT INTO customers_ VALUES
88			(@j, @tName, @tSurname, @tPhoneNumber, @tEmail, @tPhoneNumber);
89		END
90	
91	IF NOT EXISTS (SELECT * FROM orders_)
92		SET @k = 1;
93	ELSE
94		SET @k = (SELECT MAX(ID) + 1 FROM orders_);
95
96	set @tOrdertime = (select OrderTime  from tmp where ID = @i);
97	set @tOrderPrice = (select @tOrderPrice from tmp where ID = @i);
98	INSERT INTO orders_ VALUES
99			(@k, @tOrdertime, @tOrderPrice, @j);
100
101	IF NOT EXISTS (SELECT * FROM ads_)
102		SET @f = 1;
103	ELSE
104		SET @f = (SELECT MAX(ID) + 1 FROM ads_);
105    
106	set @tAdName = ( select AdName from tmp where ID = @i);
107	set @tAdPublication = (select AdPublicationDate  from tmp where ID = @i);
108	set @tAdsTextPrice = (select AdsTextPrice from tmp where ID = @i);
109	set @tAdsHashtagsPrice = (select AdsHashtagsPrice from tmp where ID = @i);
110	set @tAdsImagesPrice =  (select AdsImagesPrice from tmp where ID = @i);
111		INSERT INTO ads_ VALUES
112			(@f, @tAdName, @tAdPublication, @tAdsTextPrice,@tAdsHashtagsPrice,@tAdsImagesPrice,@k);
113	set @i = @i + 1;
114end