Menu
I am looking for a stored procedure code that will open a text file, read in several thousand lines, and add the code to a table in the database. Is there a simple way to implement this in T-SQL?
355
Syntax: SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name; This query will read the content of the text file and return it as a single column in a table named Bulkcolumn. The correlation name is mandatory to specify.
Using SQL Server Management StudioExpand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.
Go to database Oct_7_database and right click on the database. After that select Tasks, then select import data by pressing enter key. Step 5: Welcome to Wizard. Step 6: Choose a Data Source.
Open SQL Server Management Studio. Connect to an instance of the SQL Server Database Engine or localhost. Expand Databases, right-click a database (test in the example below), point to Tasks, and click Import Flat File above Import Data.
I would recommend looking at using SSIS. It's designed to do this sort of thing (especially if you need to do it on a regular basis).
Here is a good link that goes over reading a text file and inserting into the DB.
120
If the file is ready to load "as-is" (no data transformations or complex mappings required), you can use the Bulk Insert command:
CREATE PROC dbo.uspImportTextFile
AS
BULK INSERT Tablename FROM 'C:\ImportFile.txt'
WITH ( FIELDTERMINATOR ='|', FIRSTROW = 2 )
http://msdn.microsoft.com/en-us/library/ms188365.aspx
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
