10 December 2007

SQL notes

  • SELECT * FROM ES202_RUN_LKP WHERE EndDate BETWEEN CAST('01/01/2005' AS DATETIME) AND CAST('01/11/2008' AS DATETIME)
  • SELECT DISTINCT * INTO ##temp3 from ##temp2 --Use this if I want to INSERT rows AND create the destination table.
  • WHERE ##temp1.Area <> ##temp2.Area -- The 'WHERE <>' stops a cartesian join.
  • SELECT * INTO ##temp3 FROM (SELECT * FROM ##temp1 UNION SELECT * FROM ##temp2) un
  • strSQL = "IF OBJECT_ID('tblTempNIS_Qtrly_SumByRUN_and_crc', 'U') IS NOT NULL DROP TABLE tblTempNIS_Qtrly_SumByRUN_and_crc" ' This is not really a temp table because no #; so must DROP. It's a helper table. Couldn't use a temp because mData's Clear lose the data for the next query's use of it. ' The ", 'U' " is optional; it just insures that the object is a user table and not, for example, a sproc or other type of object.
  • ' To get all information about the @@Error value that is returned in the output parameter 
    ' From Master database 
    ' SELECT * FROM sysmessages WHERE Error = 2627 ' type value in rdoQy(3) here
  • --NWDSQL.BmrkSource.PendingPublish is empty for 2003-2004. Thus do this to populate it with rows from the other table:
    INSERT INTO BmrkSource.dbo.PendingPublish
    SELECT NEArea,IdentifierCode,EndDate,NumberOfPeople,Week,ItemCodeID,Ratio --Note: Published has 1 extra column at the end so I must list these out.
    FROM NELausData.dbo.Published
    WHERE NELausData.dbo.Published.EndDate LIKE '2004%'
    OR NELausData.dbo.Published.EndDate LIKE '2003%'
  • The INNER JOIN returns all rows from both tables where there is a match. If there are rows in first table that do not have matches in second table, those rows will not be listed.
  • The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second table. If there are rows in first table that do not have matches in second table, those rows also will be listed.
  • The RIGHT JOIN returns all the rows from the second table, even if there are no matches in the first table. If there had been any rows in second table that did not have matches in first table, those rows also would have been listed.
  • SQL Server is phasing out text, ntext, and image. There’s no way to know how long SQL Server will support the older data types. Upgrade legacy applications to varchar, nvarchar, and varbinary.

1 comment:

programmabilities said...

One way to eliminate dup rows at Knx was to SELECT col1, col2, col3, etc
for each column and then GROUP BY each of the columns. So maybe:
SELECT col1, col2...
INTO table1 FROM table2 GROUP BY col1, col2, etc for all columns.

header adsense code