导入 将数据从 CSV 文件导出到 SQL Server DB?

Question

我正在开发一个 Web 应用程序，我必须在其中执行导入导出功能。 我是新手，所以我希望您的建议，并且我在执行此功能时面临一些问题，

所以首先我有一个来自前端(AngularJs(的JSON数组

[
{
"MyName": "Shubham",
"UserType": "Premium",
"DialCode": "India",
"ContactNumber": "9876543210",
"EmailAddress": "[e-mail]"
"Country": "India",
"Notes": "Notes-Notes-Notes-Notes"
},
{
"MyName": "Shubham 2",
"UserType": "Free Trial",
"DialCode": "India",
"ContactNumber": "123456789",
"EmailAddress": "[e-mail]"
"Country": "India",
"Notes": "Notes-Notes-Notes-Notes"
}   ]

现在我正在使用XMLbuilder将此数组转换为NodeJs中的XML 喜欢这个

<UserXML>
<MyName>Shubham</MyName>
<UserType>Premium</UserType>
<DialCode>India</DialCode>
<ContactNumber>9876543210</ContactNumber>
<EmailAddress>[e-mail]</EmailAddress>
<Country>India</Country>
<Notes>Notes-Notes-Notes-Notes</Notes>
</UserXML>
<UserXML>
<MyName>Shubham 2</MyName>
<UserType>Free Trial</UserType>
<DialCode>India</DialCode>
<ContactNumber>123456789</ContactNumber>
<EmailAddress>[e-mail]</EmailAddress>
<Country>India</Country>
<Notes>Notes2-Notes2-Notes2-Notes2</Notes>
</UserXML>

现在我在 SQL Server 中使用此 XML 将这 2 条记录插入到我的用户表中 现在的问题是我有另一个保存国家代码和国家名称的表，我在我的用户表中使用外键 ref 我做了一个示例代码

DROP TABLE IF EXISTS #Country
DROP TABLE IF EXISTS #user
DROP TABLE IF EXISTS #UserType

CREATE TABLE #Country
(
Id INT PRIMARY KEY  identity(1 ,1),
Name VARCHAR(50) NOT NULL,
DialCode VARCHAR(50) NOT NULL
)
CREATE TABLE #UserType
(
Id INT PRIMARY KEY  identity(1 ,1),
Name VARCHAR(50) NOT NULL
)
CREATE TABLE #user
(
Id                INT PRIMARY KEY  IDENTITY(1 ,1),
Name              VARCHAR(50) NOT NULL,
UserTypeId        INT NOT NULL,
DialCodeId        INT NOT NULL,
ContactNumber     VARCHAR(50) NOT NULL,
EmailAddress      VARCHAR(50) NOT NULL,
CountryId         INT NOT NULL,
Notes      VARCHAR(50) NOT NULL
FOREIGN KEY(CountryId) REFERENCES #Country(Id),
FOREIGN KEY(UserTypeId) REFERENCES #UserType(Id)
);

INSERT INTO #Country (Name,DialCode)
VALUES ('India','+91'), 
('Dubai','+971'),
('U.S','+1') ;
INSERT INTO #UserType (Name)
VALUES ('Premium'), 
('Free Trial');

案例 1(工作正常(如果我只有一条记录，那么使用此分配没有问题

declare @xml xml = '<UserXML>
<Name>Shubham CASE-1</Name>
<UserType>Premium</UserType>
<DialCode>India</DialCode>
<ContactNumber>9876543210</ContactNumber>
<EmailAddress>[e-mail]</EmailAddress>
<Country>India</Country>
<Notes>Notes-Notes-Notes-Notes CASE-1</Notes>
</UserXML>'

现在我必须检查国家/地区名称/用户类型并将其与国家/地区表匹配以获取ID

DECLARE @CountryId INT 
,@UserType INT
SELECT @CountryId = id FROM #Country WHERE Name LIKE ''+(select U.Items.value('./DialCode[1]','NVARCHAR(200)') as DialCode FROM @xml.nodes('/UserXML') U(Items))+'%'
SELECT @UserType = id FROM #UserType WHERE Name LIKE ''+(select U.Items.value('./UserType[1]','NVARCHAR(200)') as UserType FROM @xml.nodes('/UserXML') U(Items))+'%'

INSERT INTO #user 
SELECT 
U.Item.query('./Name').value('.','VARCHAR(100)') Name,
@UserType,
@CountryId,
U.Item.query('./ContactNumber').value('.','VARCHAR(100)') ContactNumber,
U.Item.query('./EmailAddress').value('.','VARCHAR(100)') EmailAddress,
@CountryId,
U.Item.query('./Notes').value('.','VARCHAR(100)') Notes
FROM @Xml.nodes('/UserXML') AS U(Item)

CASE-2(好吧，Isssue 在这里(如果我有多个记录，那么我如何检查每个节点，然后进行连接或类似的东西以使我的插入查询正常工作

declare @xml2 xml = '<UserXML>
<Name>Shubham CASE-2</Name>
<UserType>Premium</UserType>
<DialCode>India</DialCode>
<ContactNumber>9876543210</ContactNumber>
<EmailAddress>[e-mail]</EmailAddress>
<Country>India</Country>
<Notes>Notes-Notes-Notes-Notes CASE-2</Notes>
</UserXML>
<UserXML>
<Name>Shubham 2 CASE-2</Name>
<UserType>Free Trial</UserType>
<DialCode>Dubai</DialCode>
<ContactNumber>123456789</ContactNumber>
<EmailAddress>[e-mail]</EmailAddress>
<Country>Dubai</Country>
<Notes>Notes2-Notes2-Notes2-Notes2 CASE-2</Notes>
</UserXML>'
DECLARE @CountryId2 INT 
,@UserType2 INT
SELECT @CountryId2 = id FROM #Country WHERE Name LIKE ''+(select U.Items.value('./DialCode[1]','NVARCHAR(200)') as DialCode FROM @xml2.nodes('/UserXML') U(Items))+'%'
SELECT @UserType2 = id FROM #UserType WHERE Name LIKE ''+(select U.Items.value('./UserType[1]','NVARCHAR(200)') as UserType FROM @xml2.nodes('/UserXML') U(Items))+'%'

INSERT INTO #user 
SELECT 
U.Item.query('./Name').value('.','VARCHAR(100)') Name,
@UserType,
@CountryId,
U.Item.query('./ContactNumber').value('.','VARCHAR(100)') ContactNumber,
--              U.Item.query('./EmailAddress').value('.','VARCHAR(100)') EmailAddress,
@CountryId,
U.Item.query('./Notes').value('.','VARCHAR(100)') Notes
FROM @xml2.nodes('/UserXML') AS U(Item)

请如果您有任何建议或任何其他更好的方法来完成此任务，请帮助我，我是新手，所以我不知道执行此任务的最佳方法

Answer 1

您可以直接读取 JSON 并执行JOIN

SELECT entity.*
FROM OPENROWSET (BULK N'd:tempfile.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH(
MyName nvarchar(100)
,UserType nvarchar(100)
,DialCode nvarchar(100)
,ContactNumber nvarchar(100)
,EmailAddress nvarchar(100)
,Country nvarchar(100)
,Notes nvarchar(500)
) AS entity

有关导入 JSON 文档和 OPENJSON 的更多信息