Well today I was pulled off my SharePoint Adventure and thrown on some good old database maintenance. So my “day one” turned out to be a “day none”. The only thing I’ve managed to tackle for SharePoint.. getting a Virtual Machine set up and renaming it so it doesn’t conflict with other VMs on the same network. *Tom sarcastically twirls his finger saying “wooo hoo”*

but I want to blog about something so….

Have you ever found yourself trying to import / alter some data in SQL Server but you needed  your table names to be a variable? This always happens me. My first instinct is to fire up Visual studio and write a fast and dirty asp.Net app that queries the DB.  And then i can work all sorts of my hacky magic.

To be able to stay in SQL Server Management Heaven we need to use something called Dynamic SQL.

Lets say the client gave you an excel that look something like this

And imagine that the business and money repeat all the way to 50.
You can see where the problem is now.

How would you import that into a table that looks like this?

Well we could make some SQL that imports each column

-- ## FIRST RECORD ###################################
-- Insert Into this table
INSERT     business (customer, business, amount)
-- From here
SELECT     customer, business1, money1
FROM     report

-- ## SECOND RECORD ##################################
-- Insert Into this table
INSERT business (customer,business,amount)
-- From here
SELECT 	customer,business2,money2

FROM 	report

-- ## THIRD RECORD ###################################
-- Ok.... You get the point.

That would take forever with a lot of copy and pasting!

Why not stick it all in a loop! Insert Dynamic SQL

-- ## LOOP FOR EACH CUSTOMER! ########################## --
-- Counter Variable
declare @counter int
set @counter = 0

-- The SQL Query Variable
DECLARE @sql nvarchar(4000)

-- THE LOOP!
-- *note, we put in the number of loops here!
while @counter < 50
	begin
		-- Increment the counter
		set @counter = @counter + 1

		-- Here is our SQL
		SELECT @sql =
			'
			INSERT	business(customer,business,amount)
			SELECT 	customer,
			business' + cast(@counter as char) + ',
			money' + cast(@counter as char) + '
			FROM 	report
			'

		-- Execute the SQL
		EXEC sp_executesql @sql

		print 'The current column is business' + cast(@counter as char)

	end

So as you can see. All we did here was …
- Take the SQL from before
- Wrap it in a loop with some variables.
- Build it dynamically as a string.
- Stick it in a Variable.
- Execute the string via the included SQL server stored procedure (wraps your code in some good, safe stuff)
- Tada! Awesome fun!

Dynamic SQL is neat, but it also has limitations, but for something like my import where I had no say in how the data arrived at my door. This all saved my day!

This entry was posted on Wednesday, September 24th, 2008 at 2:10 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.