Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to read from text file in SQL Server 2005

Posted on 2011-10-22
16
Medium Priority
?
204 Views
Last Modified: 2012-06-27
Dear Experts,

For some reason i have to read line by line from a text file and insert that text into a trigger at a specific time everyday. Is this is possible? then How? any special privileges are required for sql user? the file path and name is fixed....

Kind Regards,
0
Comment
Question by:jaguar4u
  • 10
  • 3
  • 3
16 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37013249
Specific privileges are always required, you need a username and password and permission to access that database.  Normally you will get that info from your database administrator.
0
 

Author Comment

by:jaguar4u
ID: 37013261
But how to read from a text file in SQL Server 2005?
I already have a username/password for database but do i need any store procedure to execute from msdb to process the text files?
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37013335
Normally you have to write an external program to open the text file and send the lines to the database.  I don't believe it will do it by itself.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:jaguar4u
ID: 37013349
We can do it either by using OPENQUERY or SSIS package, but i don't have the syntax to do it using OPENROWSET....
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37013357
You need to click on "Request Attention" above and get some other people and maybe some other zones for your question.  I haven't used either of those packages.
0
 
LVL 3

Expert Comment

by:hdesouky
ID: 37013392
Need more information to understand your problem
Are you asking to open the file to read content and insrt into database table or use the file content to create a trigger over a table?
0
 

Author Comment

by:jaguar4u
ID: 37013418
i need to open the file to read content and insrt into database table, the contents are plain text with fixed length.
0
 
LVL 3

Accepted Solution

by:
hdesouky earned 2000 total points
ID: 37013445
you can use table variables and openrowset to do this

Example code attached below

DECLARE @MyTable Table
(textLine nvarchar)

INSERT INTO @MyTable 
SELECT A.* FROM OPENROWSET(BULK 'C:\CKINFO.TXT',SINGLE_CLOB) as A


SELECT * FROM @MyTable

Open in new window

0
 

Assisted Solution

by:jaguar4u
jaguar4u earned 0 total points
ID: 37013639
Thank you for your kind response:

I used the following and it work like charm, only i have to update the SQL Server surface area configuration to allow openrowset...

Problem solved


CREATE TABLE #import (txtData nvarchar(50)) 

BULK INSERT #import
   FROM 'F:\XXXX.txt'
   WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n', FIRSTROW = 1)

SELECT * FROM #import
DROP TABLE #import

Open in new window

0
 

Author Comment

by:jaguar4u
ID: 37013733
I've requested that this question be closed as follows:

Accepted answer: 0 points for jaguar4u's comment http:/Q_27410665.html#37013639

for the following reason:

Problem solved
0
 

Author Comment

by:jaguar4u
ID: 37013644
I have already found a solution and added to the question. Hence the problem is solved now so no need for question to be up.
I don't know what is the procedure of closing a question if found the answer...

Kind Regards,

Asim
0
 
LVL 3

Assisted Solution

by:hdesouky
hdesouky earned 2000 total points
ID: 37013647
But note that your solution using templ tables and BULK INSERT which is already old style and temp tables causes troubles when something goes wrong before dropping the temp table

The table variable and openrowset is the optimum solution for your problem
0
 

Author Comment

by:jaguar4u
ID: 37013727
Thank you for further clarification, i will use table variables and openrowset.
0
 

Author Comment

by:jaguar4u
ID: 37013734
There are mutiple solutions to the questioin.
0
 

Author Closing Comment

by:jaguar4u
ID: 37043442
Problem can be solved with multiple solutions
0
 

Author Comment

by:jaguar4u
ID: 37038095
I accept what ever is suggested by community
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question