common table expression (CTE) in SQL

Have you ever used the Common Table Expression in SQL query scripts? It is very useful feature. CTE make the scripts easier to read and maintain. And it is also easier to write sql scripts with CTE for complex queries. CTEs can reference themselves.

With CTE_Table as
(
select column1, column2, column3 ... from table1 join table2 on column1 = column3
)

select *, table3.column1 from CTE_Table join Table3 on .........................


The script is very clean and simple

ArcGIS Server Application

The most recent version of ArcGIS Server is 9.3.1. It help building caches faster than before. If possible, using cache if possible, it will help to load your pages faster. Also try to have all your layers in the same coordinate system. It will make the map loading time shorter. Furthermore, do not show high-detailed layers (like parcels and local roads) in small scale. Testing your application in both Internet Explorer and Firefox. Also testing your application in different version of the same browser.

How to delete duplicate record in SQL Database

After you identify duplicate records in a table in SQL database, the next step is how to remove duplicate records.

One way to do this is to copy unique records into temporary table, then delete the original table and then copy the temporary table back.

The below is a web link about how to delete duplicate record.

Deleting Duplicate Records

If you have other ways to remove duplicate records, please share it by leaving comments.

Duplicate Record in SQL

How do you find duplicate records in a table in SQL database, here is a script

Select First_Name, Last_Name, Email, Phone, count(*)
from Table_ABC
having count(*) > 1
group by First_Name, Last_Name, Email, Phone

How to update record in a table based on values in another table

Update Table_ABC
set First_Name = (select FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)
where exists (select FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)

If the table_BCD have duplicate emails, you can pick the first one by using the following script.

Update Table_ABC
set First_Name = (select top(1) FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)
where exists (select top(1) FIRSTNAME from Table_BCD where Table_ABC.Email = Table_BCD.Email)

I found these scripts are very useful.

Xcopy Command

Do you know what Xcopy does in dos command? It is more powerful than the copy command. If you would like to copy all the files and sub-folders from one drive to another, you may need xcopy command. Of course, you can use windows explorer interface to do it. But if you would like to write batch file, and run from DOS command, Xcopy is very useful.

The below is an example listed from MicroSoft website:
------------------------------------------------------------------------

Examples

To copy all the files and subdirectories (including any empty subdirectories) from drive A to drive B, type:

xcopy a: b: /s /e

To include any system or hidden files in the previous example, add the/h command-line option as follows:

xcopy a: b: /s /e /h

-----------------------------------------------------------------------------

More information can be found on this webpage from MicroSoft Website.

List directories or file names into a text file

Sometime you may want to have a list of folders or file names into a text file for other processing. A simple way is to use DOS command to list the folders/file names and then output to a text file instead of on the screen.

For example:
C:> dir *.* >> text.txt
C:>notepad text.txt

It is very easy and simple.

Documentation

When you have a job in the work, most likely you know what the documentation means. Most of the time, we do not pay much attention to documentation. When we have a new idea, develop an application, discover a new method, we are very excited. But then we do not pay time for documentation. Later on, we were caught that we do not even understand how things work before, but not now. What procedure, when steps we forgot, ........

Documentation is very important for a job, especially programming job. please reserve some time for documentation. Even a very simple documentation can help you and other persons to understand how stuff works. It will save you a lot of time in the long run. Plus, it also help you to get more new ideas, be more good at the process.

So, when you are doing something new, it is always a good idea to document it.

SQL code

Have you ever had the need to list all the column names in a table in SQL database, do you know how to do it. Here is the code:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE_NAME'