Vinit Patel

To store Text File Contents Which is in Tabular Format, in SQL Table

Posted on: September 18, 2009


Let suppose we have in the notepad file or other type of text file the data is in the tabular format.

Like,

___________________________________________

1             xyz                  abc@info.com

2            abc                   xyz@info.com

___________________________________________

Now, If you want to store the data according to its fields like,

No.               Name                  EMailId

then do the following.

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist ‘C:\CategoryTable.txt’, @doesExist OUTPUT
SET NOCOUNT OFF
Select @doesExist

The above line is for check that if the file exists or not !!!

If the File exists then it returns the value = 1.

If yes then do the following.

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

BULK INSERT #Temp
FROM ‘C:\CategoryTable.txt’
WITH
(
FIELDTERMINATOR =’\t’,
ROWTERMINATOR = ‘\n’,
FIRE_TRIGGERS
)
END

Select * From #Temp

Drop table #Temp

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Calendar

September 2009
M T W T F S S
« Jul   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  

Stats Of Blog

  • 6,962 hits

Cluster Map

%d bloggers like this: