Solved

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

Posted on 2004-08-17
9
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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