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

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!
CochiseCountyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Melih SARICAConnect With a Mentor IT ManagerCommented:
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
 
Melih SARICAIT ManagerCommented:
U can use Bulk insert and BCP command to insert and Export data from SQL server

Check SQL Books Online for More Detail
0
 
Anthony PerkinsCommented:
Alternatively, you can use DTS.  Let us know what you want to use.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Melih SARICAIT ManagerCommented:
he ll do buk insert and bulk copy using DTS to schedule and run it.

0
 
Anthony PerkinsCommented:
>>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
 
CochiseCountyAuthor Commented:
I would like to learn using bulk insert, please show me how. Thanks.
0
 
CochiseCountyAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.