Using SQL SERVER. Create a procedure called “insertFromTXT” which takes in a sin

ID: 3816646 • Letter: U

Question

Using SQL SERVER.

Create a procedure called “insertFromTXT” which takes in a single parameter called @location of type varchar(256).

In the procedure, add code to read any text file with three columns with information about a customer’s Email, FirstName and LastName. Check the file names.txt for the general format of the data.

Create the table “Logins” to read data from a text file located at the path passed by the parameter @location. You have to use dynamic SQL to execute the BULK INSERT statement to read data from the text file to the Logins table.

To test the procedure, call the procedure with the appropriate file location path. I will provide my file path: EXEC insertFromTXT @location = 'C:UsershasmDesktopFinalProject ames.txt'

***DUE TO WEBSITE RESTRICTIONS I HAD TO EDIT A FEW OF THE PROVIDED FAKE EMAILS. BELOW IS THE INFO INSIDE OF 'names.txt':

allanSherwood@yahoo.com,Allan,Sherwood
barryz@gmil.com,Barry,Zimmer
christineb@solarone.com,Christine,Brown
davidGoldstein@hotmail.com,David,Goldstein
erinv@gmil.com,Erin,Valentino
frankwilson@sbcglobal.net,Frank Lee,Wilson
gary_hernandez@yahoo.com,Gary,Hernandez
heatheresway@mac.com,Heather,Esway

Provided outcome should look like this:

First Name Last Name Email 1 allan.sherwood@yahoo.com Allan Sherwood Barmy Zimmer 2 bamyz@gmail.com Christine Brown 3 christineb@solarone.com 4 david.goldstein@hotmail.com David Goldstein 5 erinv@gmail.com Erin Valentino 6 al net Frank Lee Wilson 7 gary hermandez@yahoo.com Gary Hemandez 8 heathereswayemac com Heather Esway

Explanation / Answer

--------- Directory-----------------------

CREATE DIRECTORY Insert_Path AS 'Path name';

------------------name.txt-------------

Email,FirstName,LastName
allanSherwood@yahoo.com,Allan,Sherwood
barryz@gmil.com,Barry,Zimmer
christineb@solarone.com,Christine,Brown
davidGoldstein@hotmail.com,David,Goldstein
erinv@gmil.com,Erin,Valentino
frankwilson@sbcglobal.net,Frank Lee,Wilson
gary_hernandez@yahoo.com,Gary,Hernandez
heatheresway@mac.com,Heather,Esway

-----------------Procedure-------------------

create or replace
PROCEDURE insertFromTXT (
i_vcFilePath IN VARCHAR2,
i_vcFileName IN VARCHAR2
)
AS

vcSql0 VARCHAR2(4000);
vcCurrentUser varchar2(1000);
vcTableName VARCHAR2(255);
vcSql VARCHAR2(4000);

BEGIN

select sys_context('USERENV','CURRENT_SCHEMA') into vcCurrentUser from dual;
vcSQL0 := 'CREATE OR REPLACE DIRECTORY Insert_Path_'||vcCurrentUser||' AS ' ||CHR(39)||i_vcFilePath ||CHR(39);
EXECUTE IMMEDIATE vcSQL0;
vcTableName := 'Logins';

vcSql := ' CREATE TABLE ' || vcTableName ||' ( Email VARCHAR2(100),FirstName VARCHAR2(50),LastName VARCHAR2(50))
           ORGANIZATION EXTERNAL
             (TYPE ORACLE_LOADER DEFAULT DIRECTORY Insert_Path_'||vcCurrentUser||'
               ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE SKIP 0 FIELDS TERMINATED BY '',''
               MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS)
              LOCATION ('||CHR(39)||TRIM(i_vcFileName)||CHR(39)||')) REJECT LIMIT UNLIMITED ' ;

INSERT INTO C VALUES(vcSql); COMMIT;
    EXECUTE IMMEDIATE vcSql;

END;

Navigate

Using SQL Lite to code, provide code solutions 8. Now insert the following two e

Using SQL Server Create an INSTEAD OF trigger named Products_UPDATE that error c

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Using SQL SERVER. Create a procedure called “insertFromTXT” which takes in a sin

Question

Explanation / Answer

Related Questions

Navigate