Access Performing ETL - Part i

Importing a text file requires determining whether the file is a comma separated values (CSV) file, fixed width file or line appended with a carriage return file. This post will focus exclusively on working with a carriage return text file containing embedded DML sql statements. The logic looks a little like this:

·         Open the text file

·         Save the contents of the current line  to appropriate variable(s)

·         Use Access’s DoCmd. RunSQl method to execute the SQL statement found in the current line in the text file

·         Continuously loop through the text file until its end

How to do it

Useful commands file I/O material

Command	Note
Line Input #	Read a line up until the carriage return and assigns it to a variable
Input #	Read CSV data into variable(s)
Input()	Input function returns all of the characters it reads and unitl the EOF –into one variable,
Write #	Data written with Write # is usually read from a file with Input #.; Writes data to a file – separate by commas
Print #	Data written with Print # is usually read from a file with Line Input # or Input
Get #	Data read with Get is usually written to a file with Put
Put #	Writes data from a variable to a disk file.
EOF(n)
Open	Open a file for input or output
Close	Close a file

This input file contains SQL statement that insert data into a table

Figure 1

Figure 2 





SQL Server Programmable objects:Triggers Trips,Trick and Traps:

Questions:

1.       Triggers are fired for which data manipulation events?

2.       What are the names of the two virtual tables that SQL Server maintains to be used with triggers?

3.       What are the different types of triggers?

4.       If an After trigger and a constraint are defined on table, which fires first:  the trigger or the constraint?

5.       Which triggers types are new to the game (since Sql Server 2000)?

6.       Which execute quicker: triggers or constraints?

7.       What keyword cancels a pending transaction within a trigger?

8.       What function will determine which column have been modified in a trigger?

9.       After records have been deleted , which virtual table would they be stored in  by SQL Server?

10.   What global variable provides the number of records most recently affected by a command?

Answers:

1.       DML events: INSERT, UPDATE AND DELETE

2.       INSERTED DELETED

3.       AFTER AND INSTEAD OF

4.       CONSTRAINTS

5.       INSTEAD OF

6.       CONSTRAINTS because they usually a LESS complex than TRIGGERS are

7.       ROLLBACK

8.       UPDATED()

9.       DELETED

10.   @@ROWCOUNT

SQL Server programmable objects: stored procedures Tips, Tricks and Traps

1.       How to view dependency info on a stored procedure

sp_depends sp_creatediagram

 

2.       How to modify a stored procedure

Alter Procedure <<stored procedure name>>

 

3.       How to find out info about a stored procedure

Sp_help <<stored procedure name>>

 

4.       How to execute a stored procedure

EXEC <<stored procedure name>>

 

5.       How to declare a variable

a.       Regular variable: DECLARE @name  varchar(120)

b.      OUTPUT variable:  see line 3 in Listing 0003

Listing 0001

1.         create proc usp_wdTestWithVariableNOutputVar

2.         (

3.         @stateCode varchar(2),

4.         @Name varchar(50) OUTPUT

5.         )

6.         as

7.         BEGIN

8.         SET NOCOUNT ON

9.         SELECT * FROM States WHERE StateCode =@STATECODE

10.        SELECT @Name='This is explicily set'

11.        END

12.                     RETURN 0

c.       T-SQL variable;

Listing 0002

1.  create proc usp_wdTestWithTSQLvariables

2.  (

3.  @stateCode varchar(2)

4.  )

5.  as

6.  --SET NOCOUNT ON

7.  BEGIN

 

8.  DECLARE @UserMsg VARCHAR(1000)

 

9.  SET @UserMsg='This is relatively pain free query'

 

10.PRINT @UserMsg

11.SELECT *

12.FROM States

WHERE StateCode=@stateCode

 

END

RETURN 0

6.       How to assign a value to a variable

a.       Use the SELECT statement: SELECT @myvariable=’hello’

b.      USE the SET statement:  SET @name=’hello’

 

7.       How to execute a stored procedure?

a.       With no variables:  EXECUTE <<stored procedure name>>

 

b.      With input variables ONLY: Using the stored procedure in Listing 0002, the following command will pass NY to the stored procedure: EXEC usp_wdTestWithTSQLvariables 'NY'

 

 

c.       With an OUTPUT variable (see Listing 0003)

Listing 0003

1.  CREATE PROCEDURE usp_wdTestWIThOutputvariables

2.  (

3.  @FirstName VARCHAR(48) = 'Anonymous',

4.  @TableName varchar(256) OUTPUT

5.  )

6.  AS

7.   

8.  SET NOCOUNT ON

9.  --Step 1 Create local temp table

10. 

11.create table #User

12.(

13.      rOWid INT NOT NULL IDENTITY ,

14.      FirstName Varchar(48),

15.      LastName Varchar(96) NOT NULL

16.)

17. 

18. 

19.-- Step 2- load sample data

20. 

21.DECLARE @NewID INTEGER

22.INSERT INTO #User(FirstName,LastName)

23.VALUES (@FirstName,'Doe')

24.SELECT @Newid=@@IDENTITY

25. 

26. 

27.RETURN @NewId

28. 

d.      Running a query In the immediate window for the query in Listing 0003

Listing 0004

Declare @rv integer

Declare @tbl varchar(255)

Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

print 'The tble name is ' + @tbl

 

e.      Saving the Return value

Listing 0005

1.  Declare @rv integer

2.  Declare @tbl varchar(255)

3.  Execute @rv=usp_wdTestWIThOutputvariables 'John',@tbl output

4.  -print 'The tble name is ' + @tbl

5.  PRINT 'The new id is ' + CAST(@rv as varchar)

 

8.       How to suppress sending info messages back to the client.

SET NOCOUNT ON

 

9.       How to write a single comment

USE the 2 dashes: --. For example,  in Listing 0002, There is a comment  in line 6

 

10.   How to write a multi-line comment?

USE /* to begin a comment block and */to end the comment black

 

11.   After finishing write a stored procedure that complies correctly, what Is the recommended next step?

Assign permissions to the new created objected by using the command:

GRANT EXECUTE ON <<stored procedure name>> TO public