Tonight, I set about blogging about something I thought was interesting, funny and marginally useful (IFmU)...or wait...funny, useful and marginally interesting (FUmI). Part of the blog post I had envisioned would be to create a database, it's objects and data to reproduce the FUmI that I wanted to impart. However...as I was creating said database I stumbled upon a new feature in SQL Server Management Studio that caused a gasping pause followed by a "No FUmIng WAY!" So, apparently no one told me that SSMS now includes an option when scripting databases to script the data too. I had to make sure so naturally I searched and despite a few obscure blog posts, there was little mention. Fair to say though, that several have already discovered this gold nugget...but I wanted to post it here for the ones who may not know.
In SSMS 2008, right click a database and select Tasks\Generate Scripts to initiate the Script Wizard. On the third page of the wizard, there it is if you scroll down..."Script Data". I scrolled thinking, 'Wow, it would be so...um...yeah cool if I saw something like ‘script data’”. And boom, there it was just like my first ever use of single quotes in double quotes like at the end of the last sentence.
So, yes, it does exist. And here is a sample of the INSERT statements created. For the record this works with 2008 and 2005 databases. I have not tried 2000 but I am hopeful. Also, it is smart enough to batch the transactions each several thousand rows.
INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AVG ', NULL)
INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'ABS ', NULL)
INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AND ', NULL)
INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BACKUP ', NULL)
GO
print 'Processed 2800 total records'
I saw several articles lately talking about a new feature in SQL Server 2008 about how we can now declare and assign variables in line without a separate SET assignment. That was in the marginally interesting (mI) bracket for me prior. That feature now falls into the "whoopie freakin do" category comparatively.
And those of you wondering about these crazy inserts, please read the subsequent blog post.