Bulk Insert/ one column

I have no problem importing a file.txt to my table (mehet).

Bulk Insert mehet From 'C:\test.txt'

With (DataFileType = 'char', FIELDTERMINATOR = ',')

But I would appreciated if someone could help me how to import only 1 or 2 columns.

instead of all columns.

Thanks.

juvan

hi,

you can perhaps have a look at format file support, that can be used by BULK operations..

define a file to be imported, say
d:\imp\data.txt like
[d:\imp\data.txt]
Andrea,Montanari,5
Juvan,Juvan,2
[/d:\imp\data.txt]

you can then use a format file to specify the required mappings... define a first one like
[fmt.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 3 Id ""
[/fmt.txt]

define another fmt file like

[fmt2.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 0 Id ""
[/fmt2.txt]

all included columns will be mapped to a column of the target table (see SQL script);

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Test (

fName varchar(10),

lName varchar(10) DEFAULT 'not set',

Id int DEFAULT -1000

);

GO

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt.txt');

SELECT * FROM dbo.Test;

GO

PRINT 'modify the fmt file putting 0 in the ID column as fmt2.txt';

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt2.txt');

SELECT * FROM dbo.Test;

GO

PRINT 'just use an INSERT ... specifying the required colums';

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

INSERT INTO dbo.Test (fName)

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

SELECT * FROM dbo.Test;

GO

DROP TABLE dbo.Test;

--<-

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

modify the fmt file putting 0 in the ID column as fmt2.txt

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

just use an INSERT ... specifying the required colums

lName

-

Andrea

Juvan

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

Andrea not set -1000

Juvan not set -1000

as you can see, you can play with the fmt file as required, excluding unwanted colums at the retrival source time, or later if you just use an INSERT SELECT FROM OPENROWSET..

regards

|||

Hi Andrea,

Thanks for your help. I got it right thanks to you.

I have one more question, if you don't mind?

Sometimes I go out of town where there has no internet. It is possible to connect to my Database at home by using

phoneline direct? If there is a way, Can you let me know please.

Thanks.

Juvan

|||

hi,

you can connect over the net to your server from your home pc... but a direct SQL Server service open over the internet is not that secure... you should eventually read about V(irtual) P(rivate) N(etwork) connections to protect your server and not to allow someone to just open a connection over your TCP/IP xxx-xxx-x-xxx IP address ...

stay secure as you can

regards

|||

hi,

Yes, but as I have said that the area where I go to has no internet connection at all. So in this case what should I do?

Thanks.

Juvan