BadriLM


Hi,

i have a file which consists data as below,

3

123||

456||

789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.

BULK INSERT TABLE_NAME FROM 'FILE_PATH'

WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||\n')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this

Thanks,

-Badri




Re: How to insert data from a file into table having two columns-BULK INSERT

DaleJ


How do you determine what goes in column1 and what goes in column2

First off, you need to make the first row in the same format as the subsequent rows...

If you want the pipes to separate the columns then try this:

Code Block

3||

123||

456||

789||

BULK INSERT TABLE_NAME FROM 'FILE_PATH'

WITH (KEEPNULLS,FIRSTROW=2,FIELDTERMINATOR ='|',ROWTERMINATOR = '|\n')







Re: How to insert data from a file into table having two columns-BULK INSERT

Naras

You can use view instead of table.

Create a view with one column from a table with two columns.

Use the view in the bulk insert.

Suppose your table structure is

Code Block

create table Table1 (col1 int, col2 int)

View is

Code Block

create view vwTable1

as

select col1 from table1

Use the above view in your bulk insert as long as your second column allows null.

Thanks

Naras.







Re: How to insert data from a file into table having two columns-BULK INSERT

hunchback

BadriLM,

If you are using SS 2005, you could use function OPENROWSET with BULK rowset provider.

insert into dbo.t1 (c1)

select c1

from openrowset(BULK ...) as a;

AMB





Re: How to insert data from a file into table having two columns-BULK INSERT

BadriLM

Hi Dale,

Thanks for the reply..

Actually iam using above bulk insert statement inside a stored procedure to fill my table having two columns. this was my actual requirement.

but i want only one column filled from bulk insert statement and other column should be filled from the parameter of the stored procedure.

the file format still remains the same as above.

can you please help on this

Thanks,

-Badri





Re: How to insert data from a file into table having two columns-BULK INSERT

BadriLM

Hi Hunchback,

Thanks for the reply.

Iam using SS2005 only, i have tried to use OPENROWSET function as below..

Code Block

insert into TABLE_NAME (C1)

SELECT C1

FROM OPENROWSET(BULK INSERT TABLE_NAME FROM 'FILE_PATH' WITH (KEEPNULLS,FIRSTROW=2,FIELDTERMINATOR ='|',ROWTERMINATOR = '|\n')) as a;

i have tried this sample, but i am getting two errors.

1) incorrect synatx near keyword INSERT

2) incorrect syntax near keyword WITH,

can you please help me on this..

Thanks,

-Badri