Bulk Insert with only one column

Hi,

I have a question on inserting data for only a specific column in a table.

I have a table as follows

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

I wanted to insert the the from a file to table.

The File contains the list of name as follows (line by line)

name1

name2

name3

name4

......

The file name actually contains the DateTime

I would like to insert the names in the file as wellas the DateTime (i.e. part of the file name ) into <MyTable>

I guess "Bulk insert " doens't allow to insert values for only one column

If i change the contents of my data file to

name1 | DateTime1

name2 | DateTime2

name3 | DateTime3

name4 | DateTime4

Then the follwoin query works fine for me.

Bulk Insert <MyTable> FROM <filePath>

With

{

FIELDTERMINATOR = '|'

ROWTERMINATOR = '\n'

}

But my original file will contains only Names and the file name contains the date that commom for all the names in the file. And also the file may contains millions of names

Is there any way this can be accomplised using " Bulk Insert" ? Or is there any alternative that i can do it fastly

Your answer will be appreciated

~mohan

Instead of achieving this task in one step, can you do in two steps making use of a staging table that matches the file structure and insert to the main table after that. There might be other solutions also, lets hear from other people.

|||

Hi Mohan,

Answer for your First Question :

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

Create a view like :

create view vwMyTable

as

select Name from MyTable

Use this view in Bulk Insert to load the data instead of the table.

Here the assumption is your DataUpdate allows Null or any default assigned to it.

Answer for your Second Question :

Insert all your data file names into a temp table using xp_cmdshell output.

Read from the table one by one and execute the bulk insert and update the datetime from table.

Thanks

Naras.

|||

If your use SQL Server 2005, you coult use OPENROWSET with BULK option:

Code Snippet

INSERT INTO youTable

SELECT a.Name, <some expression for datetime> FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

|||

You can build an SS Integraton Services package

1. For get a new datatime column you can use Derived Column Data Flow Transformation

2. For separating your data in Name and DateUpdate you can use Fixed Width Format in Flat File Connection Manager; in this way you put a column delimiter in desired position of initial string , depends on the size of DateUpdate.

This package can be put in a job a launched periodically if your business logic need it.

|||

I would also suggest that you use a stage table for the initial load, then do whatever 'massaging' needs to be done, and after that update your production table.

/Kenneth