sql-server-samples/samples/features/sql-graph/openrowset_bulk_insert.sql at master · microsoft/sql-server-samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
/*
Bulk insert using OPENROWSET
SQL Server only
*/

/* SETUP. Download [WideWorldImporters-Standard].Sales.Customers as a csv file and create the related format file
:: From a windows command line, run the following commands
bcp [WideWorldImporters-Standard].Sales.Customers out "E:\Graph Examples\Graph.Customers.csv" -w -U {userName}@{serverName} -S tcp:{serverName}.database.windows.net -P {password}
bcp [WideWorldImporters-Standard].Sales.Customers format nul -x -f "E:\Graph Examples\Graph.Customers_format.xml" -w -U {userName}@{serverName} -S tcp:{serverName}.database.windows.net -P {password}
*/

-- create node table
DROP TABLE IF EXISTS Graph.Customers;
GO

CREATE TABLE Graph.Customers(
	CustomerID int,
	CustomerName nvarchar(100) not null,
	BillToCustomerID int not null,
	CustomerCategoryID int not null,
	BuyingGroupID int null,
	PrimaryContactPersonID int not null,
	AlternateContactPersonID int null,
	DeliveryMethodID int not null,
	DeliveryCityID int not null,
	PostalCityID int not null,
	CreditLimit decimal(18, 2) null,
	AccountOpenedDate date not null,
	StandardDiscountPercentage decimal(18, 3) not null,
	IsStatementSent bit not null,
	IsOnCreditHold bit not null,
	PaymentDays int not null,
	PhoneNumber nvarchar(20) not null,
	FaxNumber nvarchar(20) null,
	DeliveryRun nvarchar(5) null,
	RunPosition nvarchar(5) null,
	WebsiteURL nvarchar(256) not null,
	DeliveryAddressLine1 nvarchar(60) not null,
	DeliveryAddressLine2 nvarchar(60) null,
	DeliveryPostalCode nvarchar(10) not null,
	DeliveryLocation geography null,
	PostalAddressLine1 nvarchar(60) not null,
	PostalAddressLine2 nvarchar(60) null,
	PostalPostalCode nvarchar(10) not null,
	LastEditedBy int not null,
	ValidFrom datetime2(7) not null,
	ValidTo datetime2(7) not null
) AS NODE;
GO

-- to improve performance, disable default indexes and SET the recovery mode to bulk_logged
ALTER INDEX ALL ON Graph.Customers disable;
GO

USE master;
GO

ALTER DATABASE [WideWorldImporters-Standard]
SET recovery bulk_logged;
GO

USE [WideWorldImporters-Standard];
GO

INSERT INTO Graph.Customers (
	CustomerID,
	CustomerName,
	BillToCustomerID,
	CustomerCategoryID,
	BuyingGroupID,
	PrimaryContactPersonID,
	AlternateContactPersonID,
	DeliveryMethodID,
	DeliveryCityID,
	PostalCityID,
	CreditLimit,
	AccountOpenedDate,
	StandardDiscountPercentage,
	IsStatementSent,
	IsOnCreditHold,
	PaymentDays,
	PhoneNumber,
	FaxNumber,
	DeliveryRun,
	RunPosition,
	WebsiteURL,
	DeliveryAddressLine1,
	DeliveryAddressLine2,
	DeliveryPostalCode,
	DeliveryLocation,
	PostalAddressLine1,
	PostalAddressLine2,
	PostalPostalCode,
	LastEditedBy,
	ValidFrom,
	ValidTo
)
SELECT
	CustomerID,
	CustomerName,
	BillToCustomerID,
	CustomerCategoryID,
	BuyingGroupID,
	PrimaryContactPersonID,
	AlternateContactPersonID,
	DeliveryMethodID,
	DeliveryCityID,
	PostalCityID,
	CreditLimit,
	AccountOpenedDate,
	StandardDiscountPercentage,
	IsStatementSent,
	IsOnCreditHold,
	PaymentDays,
	PhoneNumber,
	FaxNumber,
	DeliveryRun,
	RunPosition,
	WebsiteURL,
	DeliveryAddressLine1,
	DeliveryAddressLine2,
	DeliveryPostalCode,
	DeliveryLocation,
	PostalAddressLine1,
	PostalAddressLine2,
	PostalPostalCode,
	LastEditedBy,
	ValidFrom,
	ValidTo
FROM OPENROWSET (
	bulk 'E:\Graph Examples\Graph.Customers.csv', formatfile = 'E:\Graph Examples\Graph.Customers_format.xml'
) as temp;

USE master;
GO

ALTER DATABASE [WideWorldImporters-Standard]
SET RECOVERY FULL;
GO

USE [WideWorldImporters-Standard];
GO

ALTER INDEX ALL ON Graph.Customers rebuild;
GO

SELECT * FROM Graph.Customers;
GO