Wednesday, August 20, 2008

Yesterday Christian left me a comment containing an enhancement for the spu_generateinsert SQL. His suggestion was adding a new parameter @GenerateOneLinePerColumn to allow more "pretty" SQL to be produced - producing all the columns on separate lines which makes it easier for file comparisons to spot differences when comparing data values. I thought it made sense, so this morning I used an hour of my Madgex ILP time to make this change.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts

but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces

-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)

INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts

Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.

Labels: spu_generateinsert, SQLServer, SQLServer2005

// posted by Jane @ 6:36 PM   

Comments:

Post a Comment