Solved

Stored procedure to import/export data from SQL database to text file

Posted on 2004-08-17
9
435 Views
Last Modified: 2012-05-05
I need to write a stored procedure to export data from SQL database to text file and import data from text file to SQL database. The SQL database in is server1 and the text file is in server2. Please help. Thanks a bunch!
0
Comment
Question by:CochiseCounty
  • 3
  • 2
  • 2
9 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11821391
U can use Bulk insert and BCP command to insert and Export data from SQL server

Check SQL Books Online for More Detail
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11821436
Alternatively, you can use DTS.  Let us know what you want to use.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11827747
he ll do buk insert and bulk copy using DTS to schedule and run it.

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11831179
>>he ll do buk insert and bulk copy <<
He will?

>>using DTS to schedule and run it.<<
I suspect you mean SQL Server Agent.  DTS does not schedule anything, it uses SQL Server Agent.
0
 

Author Comment

by:CochiseCounty
ID: 11831533
I would like to learn using bulk insert, please show me how. Thanks.
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 250 total points
ID: 11838088
Here is the TSQL Description and example of Bulk Insert Command
---- From SQL Server BOL
BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }
    [ WITH
        (
            [ BATCHSIZE [ = batch_size ] ]
            [ [ , ] CHECK_CONSTRAINTS ]
            [ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
            [ [ , ] DATAFILETYPE [ =
                { 'char' | 'native'| 'widechar' | 'widenative' } ] ]
            [ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
            [ [ , ] FIRSTROW [ = first_row ] ]
            [ [ , ] FIRE_TRIGGERS ]
            [ [ , ] FORMATFILE = 'format_file_path' ]
            [ [ , ] KEEPIDENTITY ]
            [ [ , ] KEEPNULLS ]
            [ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
            [ [ , ] LASTROW [ = last_row ] ]
            [ [ , ] MAXERRORS [ = max_errors ] ]
            [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
            [ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
            [ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
            [ [ , ] TABLOCK ]
        )
    ]

Example from BOL

This example imports order detail information from the specified data file using a pipe (|) as the field terminator and |\n as the row terminator.

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

This example specifies the FIRE_TRIGGERS argument.

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )


And Also

U can create a DTS Package and add a BULK INSERT task in ur package. Then u can set the parameters of ur Bulk insert task

if u wanna schedule this inserttion u must create a job for TSQL/ Stored Proc or DTS pack.


0
 

Author Comment

by:CochiseCounty
ID: 11842346
Thanks for all of your helps, I have everthing work fine now. I am using the BULK INSERT, here is my code and it works fine

DECLARE @SQL varchar(2000)

SET @SQL = "BULK INSERT Inbox FROM 'C:\ClaimsInquiry\Outbox\"+@ProviderNumber+".txt'  "

EXEC (@SQL)

My other question is that if I want to check if the file 'C:\ClaimsInquiry\Outbox\"+@ProviderNumber+".txt'  exists before doing the bulk insert, how should I change the code?

The reason I ask is because the text file 'C:\ClaimsInquiry\Outbox\"+@ProviderNumber+".txt' is imported from a different program, that program run a search, when the result comes up, it creates the text file. I need to check if the the text file is created yet, if it's there, do the bulk insert, if not, check again, and I want to check every 5 seconds for 5 times.

Please help. Thanks
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

770 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