Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

July 2009 Blog Posts

How to insert into two tables in one statement

Today I am going to show a statement available with SQL Server 2008 and later, the MERGE command, and how it will be possible to insert into two tables within same statement. Right now, I can't see a practical use, but that might change in the future. As long as I remember this blog post exists... DECLARE @Source TABLE         (             a INT,             b INT         )   INSERT  @Source SELECT  11, 21 UNION ALL SELECT  12, 22   DECLARE @PrimaryTarget TABLE         (             a INT         )   DECLARE @SecondaryTarget TABLE         (             b INT         )   MERGE   @PrimaryTarget AS pt USING   @Source AS s ON 1 = 1 WHEN    NOT MATCHED BY TARGET         THEN    INSERT  (                             a                         )                 VALUES  (                             s.a                         ) OUTPUT  s.b INTO    @SecondaryTarget;   SELECT  'Source' AS TableName,         a,         b FROM   ...

posted @ Wednesday, July 29, 2009 6:16 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms ]

Simple base converter

Here is a simple base converter that manages [from and to] base 2 to 16. CREATE FUNCTION    dbo.fnBaseConvert (     @Value VARCHAR(8),     @FromBase TINYINT,     @ToBase TINYINT ) RETURNS VARCHAR(32) AS BEGIN     RETURN (                 SELECT     SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)                 FROM        (                                 SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue                                 FROM    master..spt_values                                 WHERE   Type = 'P'                                         AND Number < LEN(@Value)                                 HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0                                         AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase                             ) AS x                 INNER JOIN  master..spt_values AS v ON v.Type = 'P'                 WHERE       x.theValue >= 0                             AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue,...

posted @ Tuesday, July 21, 2009 1:18 AM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

How to get a batch of identity values without OUTPUT

Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch. Well, since OUTPUT is not an option for SQL Server 2000, and if you don't want to include temp tables or other means, this is one way how to solve this issue. The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved. CREATE TABLE    #Sample                 (                    ...

posted @ Thursday, July 16, 2009 11:37 PM | Feedback (4) | Filed Under [ Algorithms SQL Server 2000 ]

EXISTS is my new favorite tool

Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative. Well, today I have. Recently I blogged here http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx about finding records in one table which are missing in another table. In that blog post, I noticed that NOT EXISTS were the better choice for all scenarios, and have learnt to adopt the NOT EXISTS approach. Why haven't I extended that knowledge to the "Certain status" scenario? I can't tell. And that is a good thing. Because being active on a good forum now and then makes you rethink your positions. As I did today. Here is the test case from which...

posted @ Monday, July 13, 2009 9:36 PM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate the number of weekdays for any given period

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year. This code below calculates the number of weekdays for any given period. CREATE FUNCTION dbo.fnPeriodWeekdays (     @StartDate DATETIME,     @EndDate DATETIME ) RETURNS INT AS BEGIN     RETURN  (                 DATEDIFF(DAY, @StartDate, @EndDate) + 1                 + (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2                 - (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2                 - CASE DATEDIFF(DAY, '17530101', @StartDate) % 7                       WHEN 6 THEN 1                       ELSE 0                   END                 - CASE DATEDIFF(DAY, '17530101', @EndDate) % 7                       WHEN 5 THEN 1                       ELSE 0                   END         ) END

posted @ Sunday, July 12, 2009 2:29 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate the number of weekdays in a month

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnMonthWeekDays (     @Year SMALLINT,     @Month TINYINT ) RETURNS TINYINT AS BEGIN     RETURN (                 SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                 FROM    (                             SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 31) = 1                               UNION ALL                               SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)                             WHERE   ISDATE(10000 * @Year + 100 * @Month + 30) = 1                               UNION ALL                               SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29)                                         WHEN 1 THEN DATEADD(MONTH,...

posted @ Sunday, July 12, 2009 1:50 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to calculate number of weekdays in a year

This function calculates the number of weeksdays in a year, and has error-checking for invalid years. It is also language independant. Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATE FUNCTION dbo.fnYearWeekDays (     @Year SMALLINT ) RETURNS SMALLINT AS BEGIN         RETURN  (                     SELECT  260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)                     FROM    (                                 SELECT  DATEADD(YEAR, @Year - 1899, -1) AS dt                                   UNION ALL                                                                                                     SELECT  DATEADD(YEAR, @Year - 1899, -2)                                 WHERE   ISDATE(10000 * @Year + 229) = 1                             ) AS d                     WHERE   @Year BETWEEN 1753 AND 9999                 ) END  

posted @ Sunday, July 12, 2009 12:31 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

JDEdwards date conversion

It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year. So how to convert JDEdwards to DATETIME? DECLARE @jde NUMERIC(6, 0) SET     @jde = 107299 SELECT @jde,         DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))   And how to go from DATETIME to JDEdwards? DECLARE @Date DATETIME SET     @Date = '20041117' SELECT @Date,         1000 * DATEDIFF(YEAR, 0, @Date) + DATEPART(DAYOFYEAR, @Date)  

posted @ Friday, July 10, 2009 4:23 PM | Feedback (3) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Vacation

Finally, I am on vacation. I can't even remember when I had 4 week of contigous vacation last time! I wish the best to all of you, and see you soon again. I will sporadically visit SQLTeam and the other forums during this period. There will be some time left for me to do this after all my girls have gone to bed. //Peter  

posted @ Tuesday, July 07, 2009 7:34 AM | Feedback (4) | Filed Under [ Miscellaneous ]

Manipulate XML data with non-xml columns and not using variable

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column. Here Some XML search approaches and here Updated XML search (test case with variables). Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference. And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about...

posted @ Friday, July 03, 2009 3:57 PM | Feedback (4) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Microsoft MVP

Today it happened. I received the Microsoft MVP Award for my contributions to the Microsoft SQL Server community. I am very honored by the award and I will continue to work hard for the community to keep their trust in me. I will continue to update this blog and help users out in the SQLTeam forums, SqlServerCentral forums, SQL Server Magazine forums and SQL Server Developer Center forums among a few other. Thank you to everyone who reads this blog and leave comments....

posted @ Wednesday, July 01, 2009 6:33 PM | Feedback (8) | Filed Under [ Miscellaneous ]

Powered by:
Powered By Subtext Powered By ASP.NET