March 2009 Blog Posts

The scenario is very simple. You want to get all ID’s from one table that does not exists in another table.
This is how the two simple tables are set up.
DECLARE @Table1 TABLE
(
ID INT
)
INSERT @Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
DECLARE @Table2 TABLE
(
ID INT
)
INSERT @Table2
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION...

In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this:
DECLARE @String VARCHAR(MAX),
@Size TINYINT
SELECT @String = 'Hello my name is Jeff. I need some help on a project because ',
@String = @String + 'right now this is how the application i am working ',
@String = @String + 'with displays data.',
@Size = 32
SELECT 1 + Number AS Part,
SUBSTRING(@String, Number * @Size, @Size)
FROM master..spt_values
WHERE Type = 'P'...

Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values.
I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used.
That method is not efficient. What if you have 1 million records and there is only 1 gap at...

Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file.
I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table.
See comment in code to understand what happens
CREATE PROCEDURE dbo.uspGetFileStructureXML
(
@FileName NVARCHAR(256)
)
AS
-- Prevent unwanted resultsets back to client
SET NOCOUNT ON
-- Initialize...