SQL Server How can I add additional columns during BULK INSERT without modifying source file

xvw2m8pv 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(91)

I have a BULK INSERT into a table but I need to add data that is not in my csv for the last column in each row of the table. For example, if my csv file contains 10 columns but the database table contains 11 columns, how can I add data for the last column? Modifying the csv file itself is not an option

Is this possible with BULK INSERT ?

Here is my script:

BULK INSERT [Data_GPS].[dbo].[Data_z]
    FROM 'C:\TEMP\SQL\Bulk insert\dane.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ';',  
    ROWTERMINATOR = '\n',  
    ERRORFILE = 'C:\TEMP\SQL\Bulk insert\errors.txt',
    TABLOCK
    )

sql-server

来源：https://stackoverflow.com/questions/67004594/how-can-i-add-additional-columns-during-bulk-insert-without-modifying-source-fil

1条答案

按热度按时间

js81xvg61#

Sure. Just use OPENROWSET(BULK ...) instead of BULK INSERT and you can add whatever extra columns you want. EG

INSERT INTO dbo.myFirstImport  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;

But you have to create a format file for openrowset.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How can I add additional columns during BULK INSERT without modifying source file

1条答案

相关问题

热门标签

最新问答