如何將數據垂直批量插入sql server？

我的數據格式如下。我必須插入到 sql server 表中。

agency    NO
booknbr   06000330
bookdttm  2006-07-19 10:56:00
arrdttm   2006-05-07 05:42:00
aj_id     A000009454
casenbr   06006640
off1      619
off2      634
arrplace  DENNYS RESTAURANT
howarr    O
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000331
bookdttm  2006-07-24 12:11:00
arrdttm   2006-07-16 16:11:00
aj_id     A000003215
casenbr   06010336
off1      641
off2      
arrplace  219 W GLENCOVE AV
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000332
bookdttm  2006-07-25 15:11:00
arrdttm   2006-06-13 22:47:00
aj_id     A000009455
casenbr   06008615
off1      624
off2      
arrplace  113 JULIE DR
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N

您可以使用 BULK INSERT 並提供如下所示的 FIELDTERMINATOR 和 ROWTERMINATOR 值，這會將數據插入到正確的列中。唯一的缺點是數據還包括列名。一個簡單的游標來清理它，數據看起來很好。

您可能希望插入到臨時表中，因為所有數據類型在插入時都是 VARCHAR，然後您可以在清理後插入到具有正確數據類型的最終數據表中。

創建表來儲存數據：

CREATE TABLE dbo.SampleCSVTable
(
   agency    VARCHAR(255),
   booknbr   VARCHAR(255),
   bookdttm  VARCHAR(255),
   arrdttm   VARCHAR(255),
   aj_id     VARCHAR(255),
   casenbr   VARCHAR(255),
   off1      VARCHAR(255),
   off2      VARCHAR(255),
   arrplace  VARCHAR(255),
   howarr    VARCHAR(255),
   juvstat   VARCHAR(255),
   ko        VARCHAR(255),
   remarks   VARCHAR(255),
   armed     VARCHAR(255),
   federal   VARCHAR(255),
)
GO

批量插入數據：

BULK INSERT dbo.SampleCSVTable
FROM 'D:\SQLDATA\SQL01DEV\SampleData.csv'
WITH
(
   FIELDTERMINATOR = '\n',
   ROWTERMINATOR = '\n\n'
)
GO

清理數據（此游標循環遍歷每一列並從插入的數據中刪除列名）：

DECLARE @ColName NVARCHAR(255),
   @SqlCmd NVARCHAR(255)

DECLARE C1 CURSOR FOR
   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SampleCSVTable'

OPEN C1

FETCH NEXT FROM C1 INTO @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SqlCmd = 'UPDATE dbo.SampleCSVTable SET [' + @ColName + '] = LTRIM(RTRIM(REPLACE([' + @ColName + '], ''' + @ColName + ''', '''')));'
   EXEC sp_executesql @SqlCmd

   FETCH NEXT FROM C1 INTO @ColName
END

CLOSE C1

DEALLOCATE C1

查詢數據：

SELECT * FROM dbo.SampleCSVTable

結果：

agency  |   booknbr     |   bookdttm            |   arrdttm             |   aj_id       |   casenbr     |   off1    |   off2    |   arrplace            |   howarr  |   juvstat |   ko  |   remarks |   armed   |   federal
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NO      |   06000330    |   2006-07-19 10:56:00 |   2006-05-07 05:42:00 |   A000009454  |   06006640    |   619     |   634     |   DENNYS RESTAURANT   |   O       |           |       |           |           |   N
NO      |   06000331    |   2006-07-24 12:11:00 |   2006-07-16 16:11:00 |   A000003215  |   06010336    |   641     |           |   219 W GLENCOVE AV   |   V       |           |       |           |           |   N
NO      |   06000332    |   2006-07-25 15:11:00 |   2006-06-13 22:47:00 |   A000009455  |   06008615    |   624     |           |   113 JULIE DR        |   V       |           |       |           |           |   N

引用自：https://dba.stackexchange.com/questions/251089