SQL Server BULK INSERT Using a Data Field?

I have wondered for a while if there is a way to use SQL Server’s BULK INSERT command using BLOB or CLOB data Instead of a local file.

The reason for this is that when writing imports, using both Coldfusion and PHP, parsing and importing the data into the database is the weakest link.  It is relatively simple to write dynamic SQL to import data into my existing schema when copying from data already present in the database.  It is quite another matter to attempt to run thousands of INSERT statements from my web application.

I can’t ask a client to use SQL Management Studio to import their data into a table and then use a web app to run the import.  I don’t want to expose the database to the user and vice-versa.

At present I can only think of one good way to implement an import process and that is to allow the user to

1. Upload the file.
2. Create some sort of import plan (associate the content).
3. Save that plan in some way.
4. Run a scheduled task to run the import in batches.

I am dealing with imports of possibly hundreds of thousands of records.  Also note that I have limited access to the database as it is possible that the app will reside in a shared hosting environment.

MS SQL Server Time Dimension UDF

I use this function when writing reports.

Use:

SELECT
	time.Date
	, Count(tab.ID) AS [Count]
FROM
	dbo.TimeDimension('1/1/2007 00:00:00', '12/31/2007 23:59:59', 'month', 1) time
	LEFT OUTER JOIN
		TempTable tab
	ON tab.DateCreated BETWEEN time.Date AND time.DateEnd

Here is the function. I don’t know if there is a better way to do this, if there is can someone tell me. As far as I know I am the only person to post a function like this.

CREATE FUNCTION [dbo].[TimeDimension] (
	@DateStart datetime
	, @DateEnd datetime
	, @Type varchar(20)
	, @IncrementValue int
)
RETURNS @Dates TABLE ( Date datetime , DateEnd datetime )
AS
BEGIN
	DECLARE @DateCurrent datetime, @NextDate datetime
	SET @DateCurrent = @DateStart

	WHILE (@DateCurrent <= @DateEnd)
	BEGIN
		SET @NextDate = CASE (@Type)
			WHEN 'year' THEN DateAdd(year, @IncrementValue, @DateCurrent)
			WHEN 'quarter' THEN DateAdd(quarter, @IncrementValue, @DateCurrent)
			WHEN 'month' THEN DateAdd(month, @IncrementValue, @DateCurrent)
			WHEN 'dayofyear' THEN DateAdd(dayofyear, @IncrementValue, @DateCurrent)
			WHEN 'day' THEN DateAdd(day, @IncrementValue, @DateCurrent)
			WHEN 'week' THEN DateAdd(week, @IncrementValue, @DateCurrent)
			WHEN 'weekday' THEN DateAdd(weekday, @IncrementValue, @DateCurrent)
			WHEN 'hour' THEN DateAdd(hour, @IncrementValue, @DateCurrent)
			WHEN 'minute' THEN DateAdd(minute, @IncrementValue, @DateCurrent)
			WHEN 'second' THEN DateAdd(second, @IncrementValue, @DateCurrent)
			WHEN 'millisecond' THEN DateAdd(millisecond, @IncrementValue, @DateCurrent)
		END

		INSERT INTO @Dates ( Date , DateEnd )
		VALUES ( @DateCurrent , DateAdd(second, -1, @NextDate) )

		SET @DateCurrent = @NextDate
	END
	RETURN
END

MD5 Hash in SQL Server 2000 and 2005, 32 bit and 64 bit

SQL Server 2000
xp_MD5 at http://www.codeproject.com/database/xp_md5.asp

Also works in SQL Server 2005 32 bit

SQL Server 2005

DECALRE @Data varchar(MAX)
SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @Data)), 3, 32)

Works in SQL Server 2005 32 and 64 bit

Solution found at http://www.codeproject.com/database/xp_md5.asp?df=100&forumid=32460&select=1519990#xx1519990xx

• Tags

  cms code git sql wishlist

• Archives

  • January 2010
  • September 2008
  • August 2008
  • July 2007
  • June 2007
  • February 2007

• Blogroll

  • GSL Solutions