How to read data and write data from and into file in ssms , stored procedure

Question

0.00/5 (No votes)

See more:

Hi all

Just want to right a stored procedure, that will read six column from .txt file and then insert into temp table , than i have to do some transformation on data , than i want to write this data back in to four files

so i have a column whos values are A,B,C,D so i have to write 4 files and i want to run this from job agent which is not a problem , and is there a way in stored procedure to directly read or paste file from ftp server

Please any help would be much appriciated

Thanks

Posted 2-Apr-15 4:15am

saad88

Add a Solution

Comments

saad88 2-Apr-15 10:18am

so columns are email,address,cell,type,version .... so i have to add version column by my self ..... so any rough code idea would be much appriciated , the scope of project is to take invalid data out and insert few things in , i have Pentaho ETL but i am looking for stored procedure

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mogaveer · Answer 1 · 2015-04-17T02:42:00

Hey,
I've two set of codes which might help you to resolve the issue.
First one is to read the Data from a Text File.

SQL

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, N'Jet 4.0', 
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\',
   NULL,
   N'Text';
GO

----Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, sa, NULL;
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv;
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv...[distqry#txt];

Now you'll get the data in a table and that you can copy to a temp table. Process it however you wanted and create the text
and Here is another set of code to write text Data into a file and create a file from SQL Server.

SQL

DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world'
SET @Cmd ='echo ' +  @Text + ' > e:\AppTextFile.txt'
print @cmd
EXECUTE Master.dbo.xp_CmdShell  @Cmd