My MySQL tool is on softpedia.com.
I just got the notice today. I wrote this bulk row insert tool for MySQL that is designed to run over the Internet. It’s for really large data sets being loaded into … ahem … inexpensive hosted MySQL databases. Check out the mysqlxfer softpedia page.
The mother of all dynamic SQL Crosstab View Builders.
Ok, so maybe it’s not too poetic, but it’s descriptive. As I found myself writing a dynamic crosstab-query builder in MS SQL for probably the fifth time of my career, I decided I’d post it here to save you all just a little time in your day. Make sure you use the saved time for something I’d approve of (smoking a good cigar, playing a little poker, something like that).
This proc is designed for when you have a dynamic set of row-oriented data you want to pivot and show column-wise. It takes four parameters:
@tableName – Name of the source table (or view) that will be queried by the new crosstab view.
@excludeCols – A comma separated list of columns to exclude from the crosstab. In most cases, will be the ID column of the table.
@valueColName – Name of the column in the @tableName table that contains the value in a given row.
@targetViewName – Name of the view that the stored procedure will create. It will *automatically* drop this column before re-creating it, so be warned.
This proc also assumes that the @tableName table (or view) has a column called [colName]. It uses this value to create the corresponding columns across the top of the output view.
Good luck!
ALTER PROCEDURE BuildCrosstabView(
@tableName VARCHAR(100),
@excludeCols VARCHAR(1000),
@valueColName VARCHAR(1000),
@targetViewName VARCHAR(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @cols VARCHAR(4000)
SET @cols = ''
EXEC ('SELECT DISTINCT colName INTO ##colList FROM ' + @tableName)
DECLARE col_cursor CURSOR FOR
SELECT colName
FROM ##colList
OPEN col_cursor
DECLARE @colName VARCHAR(100)
FETCH NEXT FROM col_cursor INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @cols <> ''
BEGIN
SET @cols = @cols + ', '
END
SET @cols = @cols + 'MAX(CASE colName WHEN ''' + @colName + ''' THEN ' + @valueColName + ' ELSE NULL END) AS [' + @colName + ']
'
FETCH NEXT FROM col_cursor INTO @colName
END
CLOSE col_cursor
DEALLOCATE col_cursor
DROP TABLE ##colList
DECLARE @sql VARCHAR(3000)
SET @sql = 'SELECT DISTINCT ' + @excludeCols + ', ' + @cols + ' FROM ' + @tableName + ' GROUP BY ' + @excludeCols
PRINT @sql
EXECUTE (@sql)
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[' + @targetViewName + ']'))
EXECUTE ('DROP VIEW ' + @targetViewName)
EXECUTE ('CREATE VIEW ' + @targetViewName + ' AS ' + @sql)
END
GO
Bulk SQL inserting, anyone?
As part of the BlinkStory app, I needed to upload about 200K (200 thousand, for the geek-impaired) records into a MySql database hosted on a very inexpensive (cheap) hosting provider. I’d tried several options to get the data in, with frustrating results. Either my file upload would be interrupted, or an error would occur around row 75K, wasting an hour or so of my time.
So I decided, like any good programmer, to roll my own solution. I created a tool for bulk uploading SQL over the Internet that I call mysqlxfer. Not wanting to hoard it, I submitted it to SourceForge.net as an open source project. If you need to upload a bunch of data over an unreliable and slow connection, give it a shot. It does a couple of cool things, like automatically detecting your field separator character and logging the failed inserts in SQL format to a .err file. Check it out!
posts feed