Quickly import EXCEL data to tempdb table and script out field descriptor

\ by

USE tempdb
GO

SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$

--
-- Use Query Analyzer to script out the table in tempdb.
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[MyEXCELSheet] (
    [CustomerID] [nvarchar] (255) NULL ,
    [EmployeeID] [float] NULL ,
    [Freight] [float] NULL ,
    [OrderDate] [datetime] NULL ,
    [OrderID] [float] NULL ,
    [RequiredDate] [datetime] NULL ,
    [ShipAddress] [nvarchar] (255) NULL ,
    [ShipCity] [nvarchar] (255) NULL ,
    [ShipCountry] [nvarchar] (255) NULL ,
    [ShipName] [nvarchar] (255) NULL ,
    [ShippedDate] [datetime] NULL ,
    [ShipPostalCode] [float] NULL ,
    [ShipRegion] [nvarchar] (255) NULL ,
    [ShipVia] [float] NULL
) ON [PRIMARY]
END

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO

Posted in: ETL, SQL

Determine leap year SQL function

\ by

Here's one way, as inspired by Anatoly Lubarsky.


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE  FUNCTION dbo.fn_IsLeapYear (@dtDate DATETIME)
RETURNS BIT
AS
BEGIN
    DECLARE @dtFeb28 DATETIME,
        @nMonthDay tinyint
    --
    -- Add a day to the 28th of February and see if it is the 1st or the 29th.
    SELECT     @dtFeb28 = CONVERT(VARCHAR(4), YEAR(@dtDate)) + '0228',
        @nMonthDay = DATEPART(DAY, DATEADD(DAY, 1, @dtFeb28))

    IF (@nMonthDay = 29)
    RETURN 1

    RETURN 0  

-- Usage:
--     SELECT dbo.fn_IsLeapYear (GETDATE())
--     SELECT dbo.fn_IsLeapYear (dateadd(year,1,GETDATE()))
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Posted in: SQL

Supported SQLCMD Syntax

\ by

Supported SQLCMD Syntax
Query editor supports the following SQLCMD script keywords:

[!!:]GO[count]

!!

:exit(statement)

:Quit

:r

:setvar

:connect server[\instance] [-l login_timeout] [-U user [-P password]]

:on error [ignore|exit]

:error |stderr|stdout

:out |stderr|stdout

Note:
For both :error and :out, stderr and stdout send output to the messages tab.

SQLCMD commands not listed above are not supported in Query Editor. When a script containing SQLCMD keywords that are not supported is executed, the Query Editor will send an “Ignoring command ” message to the destination for each unsupported keyword. The script will execute successfully, but the unsupported commands will be ignored.

Caution:
Because you are not starting SQLCMD from the command line, there are some limitations when running Query Editor in SQLCMD Mode. You cannot pass in command-line parameters such as variables, and, because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

Posted in: Uncategorized

CONSTRAINT or INDEX?

\ by

This is the best explanation I've seen so far on this topic. Explains there is really no difference and that we should use CONSTRAINTs when data integrity is the objective.

SQL Server 2005 Books Online (September 2007)
Creating Unique Indexes

Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE constraint on the column when data integrity is the objective. This makes the objective of the index clear.

Posted in: SQL