CODEWITHSUNDEEP
sqlsql-serverbulkinsert
ter24
ter24

Reputation: 462

Bulk Insert into SQL Server from text file adding a uniqueidentifier, or skipping a column

I need to take rows from a text file and insert into a table. The rows of the file are similar to the following:

string1
string2
string3
string4
...

My table has 2 columns: 

uniqueidentifier, stringValue

I would like to do a bulk insert into the table grabbing each row from the text file and adding a new uniqueidentifier to each one (ideally a GUID, but an integer counter would be fine).

Anyone have a tip on how to do this bulk upload? So far my SQL is:

BULK
INSERT tableNameToInsertInto
FROM 'C:\importList.txt'
WITH
(
ROWTERMINATOR = '\n'
)
GO

Upvotes: 3

Views: 8327

Answers (1)

Devart
Devart

Reputation: 121902

Try this one -

Values:

C:\importList.txt ->
string1
string2
string3
string4

Format file:

C:\importList.fmt ->
11.0
1
1     SQLCHAR     0     100     "\r\n"     1     text     ""

Query:

CREATE TABLE dbo.testBulk 
(
      RowUID UNIQUEIDENTIFIER DEFAULT NEWID()
    , Value VARCHAR(100)
)

INSERT INTO dbo.testBulk (Value)
SELECT c.[text]
FROM OPENROWSET(
    BULK N'C:\importList.txt', 
    FORMATFILE = 'C:\importList.fmt'
) c

Upvotes: 4

Related Questions