Solved

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

Posted on 2004-08-17
9
427 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now