July 2009 Blog Posts
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 ...
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,...
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
(
...
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...
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
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,...
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
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)
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
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...
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....