使用共享密钥插入多个表

2024-05-14 • 问答

我需要在电话簿中插入多个用户和电话号码。我将用户及其电话号码存储在.csv文件中，并且需要将此数据添加到共享FK uniqeidentifier的多个表中。标识符是在“用户”表中创建用户时生成的。

我该如何实现？
如何最大程度地实现这一目标有效率的方法吗？

我尝试过的事情和我想做的事情（但这不起作用）：

CREATE TABLE Users(
UserID uniqeidentifier NOT NULL  
   DEFAULT newid(),Firstname nvarchar (25),Lastname nvarchar(25)
)
GO

CREATE TABLE Phonebook(
Number nvarchar (25),UserID uniqeidentifier
)
GO

CREATE TABLE #TempImportedUsers(
firstname nvarchar(25),lastname nvarchar(25),phonenumber nvarchar(25)
)
GO

BULK INSERT #TempImportedUsers
    FROM 'D:\import.csv'
    WITH
    (
    FIRSTROW = 2,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',Tablock
    )
    GO

INSERT INTO Users (Firstname,Lastname)
OUTPUT #TempImportedUsers.phonenumber,Inserted.UserID INTO Phonebook(Number,UserID)
SELECT
    firstname,lastname
FROM
    #TempImportedUsers

D：\ import.csv的内容：

Firstname,Lastname,Number
Foo,Bar,3311664499
Baz,Qux,8844331166

显然，由于OUTPUT #TempImportedUsers.phonenumber,UserID)无效，以上操作无效。

如何实现此导入？我必须使用游标吗？

您可以通过将UserID生成放入#temp表中来解决该问题。这是下面的导入示例，

最简单的方法是使用OPENROWSET(bulk,...)而不是批量插入，并使用临时表。对于此示例，这是临时表：

CREATE TABLE #TempImportedUsers(
    UserId uniqueIdentifier DEFAULT NEWID(),firstname nvarchar(25),lastname nvarchar(25),phonenumber nvarchar(25)
);

这是相关的格式文件（XML格式是最简单的方法）：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="phonenumber" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

此XML格式文件有一个技巧-忽略表的第1列。这将导致自动生成UUID。

这是我使用的示例文件：

First,Last,Phone
Bob,Smith,333-444-6666
Bill,222-333-5555
Alice,Restaurant,91-03045-2222

这是INSERT查询，用于加载#temp表：

INSERT INTO #TempImportedUsers (firstname,lastname,phonenumber)
SELECT *
FROM OPENROWSET(bulk 'D:\import.csv',formatfile='D:\formatfile.xml',FIRSTROW=2) as t;

SELECT * FROM #TempImportedUsers查询的结果是：

|UserId                              |firstname|lastname  |phonenumber  |
|------------------------------------|---------|----------|-------------|
|9514B27F-1C6B-4B18-BE63-4E7744B9484B|Bob      |Smith     |333-444-6666 |
|108AA33C-829B-4E7E-BE62-C88F1F77853A|Bill     |Smith     |222-333-5555 |
|9C6AD6FD-6F26-471E-A9B8-CE5F8F4B1266|Alice    |Restaurant|91-03045-2222|

希望这会有所帮助。

当在评论中提出建议时，我发现一个简单的解决方法是生成自己的Guid / uniqeidentifier，因为这始终是用户和电话簿表中使用的内容。

以防万一有重复的Guid，我将其全部包装在BEGIN TRANSACTION语句中以处理整个导入的回滚。最后选择导入的用户并删除#temptable。

CREATE TABLE #TempImportedUsers(
firstname nvarchar(25),phonenumber nvarchar(25),guid uniqueidentifier DEFAULT newid()
)
GO

BULK INSERT #TempImportedUsers
    FROM 'D:\import.csv'
    WITH
    (
    FIRSTROW = 2,FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',Tablock
    )
    GO

BEGIN TRANSACTION [Tran1]
    BEGIN TRY
INSERT INTO Users (Firstname,Lastname,UserID)
SELECT
    firstname,guid
FROM
    #TempImportedUsers

INSERT INTO Phonebook(Number,UserID)
SELECT
    number,guid
FROM
    #TempImportedUsers
COMMIT TRANSACTION [Tran1]
    END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1]
END CATCH

SELECT * FROM #TempImportedUsers
DROP TABLE #TempImportedUsers

import.csv更新为新的#temptable列提供NULL值：

Firstname,Number,Guid
Foo,Bar,3311664499,Baz,Qux,8844331166,

在最初的问题中，我问了两个问题；如何做到这一点以及如何最有效地做到这一点。这解决了如何实现它，但是我不知道是否还有更有效的方式（资源效率或书面行数）-因此我将把这个问题待一会儿。

使用共享密钥插入多个表

daohaoa 回答：使用共享密钥插入多个表

大家都在问