Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-08-17
9
Medium Priority
?
454 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

927 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