时间戳

首页 > 解决方案 > 使用共享键插入多个表

sql - 使用共享键插入多个表

问题描述

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

  1. 我怎样才能做到这一点？
  2. 我怎样才能以最有效的方式实现这一目标？

我尝试过的以及我想做的（但这不起作用）：

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,Inserted.UserID INTO Phonebook(Number, UserID)方法无效，因为无效。

我怎样才能实现这种导入？我必须使用游标吗？

标签： sqlsql-servertsqlimportinsert

解决方案

您可以通过将 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,Smith,222-333-5555
Alice,Restaurant,91-03045-2222

这是加载 #temp 表的 INSERT 查询：

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|

希望这会有所帮助。

推荐阅读