Common Solutions for T-SQL Problems – MSDN

There are some great articles on MSDN on solutions to common problems people have (though the array solution would be better done with a Tally table). Here is their page with a list of common questions, I think every good developer should take the time to read through these.
Common Solutions for T-SQL Problems – MSDN

Advertisements

Leave a comment

“Tally” tables or “Numbers” tables are pretty great

I recently read an article on ‘Tally’ tables. They are amazingly useful, if you have need to calculate anything in a looping fashion in SQL you really should look into them. The way a Tally table works is you create a table with unique numbers from 1 to x (where x is a very large number, 11,000 is suggested as it is enough numbers to store 365.25 days times 30 years), once you have this table you join it to your query for a super fast tally of numbers.

It seems bizarre at first, but the reason this concept is so fast is it doesn’t have to spend time calculating and waiting. A good example of a practical implementation of a Tally table is for use in situations where you want to pass an array to a sql statement, ever tried? SQL Server does not have the concept of arrays so passing one in requires a bit of a mashup. There are a few methods people will try to do this, one is a comma/semi colon delimited list, eg:
item1,item2,item3,etc
then in the stored proc they will have a loop that finds each comma and execute sql on it.
– or –
They will write dynamic sql, like so:

string sMyList = "'item1','item2','item3'" //in practice this would be constructed in a loop
string sSQL = "select column1, column2 from tableX where column1 in (" + sMyList + ")"
exec sSQL

Both of these methods have issues, the first query one is slow (it’s looping through items), the second query will never result in the SQL engine pre compiling it (the query is different on every run) which also makes it slow. Not to mention that both methods can have SQL injection issues.

So both methods have issues, so what then do we do to make a tally table work?

For example consider this SQL statement:

DECLARE @Parameter VARCHAR(8000) 
    SET @Parameter = ',Element01,Element02,Element03,' --Note the requirement to have commas both on the end and beginning of the parameter
 Set Nocount ON
 SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
   FROM dbo.Tally
  WHERE N < LEN(@Parameter)             
    AND SUBSTRING(@Parameter,N,1) = ',' 

This query is run for each row in our Tally table simultaneously, this means no looping, or waiting for computation and results, instead just instant results. What in essence it’s doing is saying “Get me all the rows in the Tally table where the number is less than the length of the parameter passed in.” At this point we have a list of numbers, 1,2,3,4,etc, the list is long enough that if every entry in it was a comma it would still parse the results. Once it has the list of numbers it uses it on each row simultaneously to figure out “is this character a comma? If so show everything after it up to the next comma”. It does this compare for every character in the string.

It’s blazingly fast, my post does not do the concept justice, but I felt why rewrite a post when it’s already been written so well by others? Here’s an article on the basics of Tally tables The “Numbers” or “Tally” Table: What it is and how it replaces a loop, and another article that builds on the concept, showing how to parse a string into multi dimensional array: Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays.

Thanks to SQL Server Central, parts of the code in this post are copied from the articles I’ve linked.

Leave a comment

Internet Storm Centre

I found this site today, looks like it will be interesting to keep an eye on. The site appears to keep track of when the internet is having issues with malware. They spelled centre wrong though 🙂
http://isc.sans.edu/diary.html

Leave a comment

How to write a Trigger in SQL Server

Just a quick post with a simple example trigger I put together. I’ve noticed allot of people seem to think that cursors are necessary to write a trigger, not so. In fact cursors are a bad way of doing things in general. They’re slow and run one command at a time, they loose out on the speed increase of running an update in batch. Al-Farooque Shubho touches on this concept in his article “Understanding “Set based” and “Procedural” approaches in SQL”.

Here’s the trigger, it’s written to audit changes on a date field. You need to create a table to handle the insert for this. Remember two things here:
1) The new table needs to have exactly the same data types (and null ability) of the parent table, but NOT the default values.
2) The primary key of the origin table will NOT be the same in the audit table. This is because a row can be changed multiple times, auditing those changes means the same primary key being inserted over and over. In my example I create a new column called EventOccuranceTime which is defaulted to getdate(). I then used the primary tables unique identifier plus the EventOccuranceTime as the PK in the new table. Note that triggers can seriously slow down your data access, putting a primary key on the audit table will slow down updates / inserts into the parent table more yet. If this speed reduction is an issue for you you may not want a primary key on the audit table, just take the hit when reading from the data (no index) instead of writing it with an index. I’ve included the table create script below as well:

CREATE TABLE [dbo].[SomeTable_audit](
	[ROWID] [int] NOT NULL,
	[EventOccuranceTime] [datetime] NOT NULL,
	[FromDATE] [datetime] NULL,
	[ToDATE] [datetime] NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
(
	[ROWID] ASC,
	[EventOccuranceTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SomeTable_audit] ADD  CONSTRAINT [DF_SomeTable_audit_EventOccuranceTime]  DEFAULT (getdate()) FOR [EventOccuranceTime]
GO

Something to know about triggers, they have two special tables available in them. One is the rows inserted, the other the rows deleted. If you made updates then they are tables that hold all the rows that were updated (treating old data as deleted and new data as inserted), even if only one column was updated.
If you change this trigger to include inserts and deletes the join below will thwart that. You will need to change this join to allow left and / or right joins depending on inserts / deletes, or write multiple queries, one for inserts, one for deletes, one for updates. You can write more than one query in a single trigger.

create trigger dbo.audit_SomeTable on dbo.SomeOriginTable for update -- This could include insert and delete, mine only handles updates.
as
if update(desired_date) --if the desired_date field is changing do the insert
begin
--Code thanks to https://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
	insert into dbo.SomeTable_audit (rowid,FromDATE,ToDATE) 
	select i.rowid,d.desired_date,i.desired_date
		from inserted i
	join deleted d on
		i.rowid = d.rowid 
	where datediff(day,d.desired_ship_date,i.desired_ship_date)<>0 --added because update() doesn't always seem to work, 
	--I believe it determines it's being updated if it's passed in the query, not if it's actually different.
end
go

Here’s a great MSDN magazine article with more examples of triggers (including triggers that prevent certain kinds of updates):
Exploring SQL Server Triggers.

Leave a comment

Excellent website – catacombae. Find Duplicate files on your computer, get your DiskUsage, etc

I just found an excellent website, www.catacombae.org, on it Eric posts a few different useful utilities and the code is open source. I have used three of his utilities and they work great, the three I’ve used are:

1) DiskUsageAnalyzer
I used to use FolderSize for this, unfortunately Microsoft made a change in Windows 7 that no longer allows extending explorer in a way that allows this add-on to work. /shake fist @ MS. This utility works quite well, it’s great if your disk is getting full and you don’t know where all the space is being used. IMO this is a utility that should be built into windows.

2) FindDuplicates
This is actually an idea I wanted to write for ages, managing all those family photos and backups is a pain (we tend to copy photos we want to get printed to a temp to print folder, and then promptly forget about them). Unfortunately this app shows errors as they occur meaning it stops processing on every error. You’d think this wouldn’t be a issue, but in practice there are lots of reasons for this process to fail on files (locked files, permissions). It would be great if it would just build a list of errors and continue processing instead of making me click ‘Ok’ over, and over, and over. Great app for the price though!

3) HFSExplorer
HFSExplorer is a great little utility for reading Mac based hard drives on a PC. Works great. Though I’m told that Windows 7 will read HFS disks so this utility seems to only be useful on pre Windows 7 machines.

Thanks for the great utilities Erik!

1 Comment

Visual Results – how to batch recalculate all sub engineering masters included on other engineering masters

In Visual Results you can create engineering masters that you then use to include on other engineering masters. To do this you open the child master and click the ‘Add Leg/Detail’ button. In this window you choose the sub engineering master and poof, you now have an engineering master that includes a sub engineering master.

While a neat feature, these sub masters can be problematic unfortunately. The issue is if you have multiple sub masters that contain one parent and you want to change the parent how do you get the sub masters to reflect the change?

This is where it’s tricky, Visual does not store enough information to know what engineering master the sub master came from. It doesn’t ‘link’ the sub master, it actually imports all the operations / requirements from the sub master into the master. The only remnant of what master was imported is the black line that says the master’s base id NOTE it does not know the Lot ID so if you have multiple masters with the same base and different lot this becomes impossible to do with 100% accuracy.

With this in mind, I was asked to update all masters that linked sub masters as my clients needed to do a large update. Fortunately they didn’t have any masters with a lot id other than 0 so I was able to link what sub master(s) each link came from. In the end this wound up being more complicated as they did override the sub masters in a number of places and didn’t want the overrides deleted.

I chose to write two queries, one that would delete all items on a master that came from sub masters, and one that would insert all items from sub masters on to masters. This proved very effective, and fast enough I was happy with it. It would of been better to write update quieries, but that gets quite a bit more complicated as I would have to update all matching items, delete all extra items no longer on the sub master, insert all items on the sub master not on the base master. All of those queries would have to know how to exclude the other situations. Deleting / Inserting worked well, was reliable and did exactly what we needed it to so that’s what this solution uses. If you wind up extending this with update queries I’d love to see your results!

Here is the query that deletes all items from a master that came from a sub master:

delete requirement from 
(
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
--If you wanted to ignore some parts this is how you would write it, note that this can cause issues if the piece_no, if you want to do all then omit the where.
where requirement.part_id not like 'X7%'
and (requirement.part_id not like 'X1%' or requirement.part_id='X1B1' or requirement.part_id='X1R1')
and (requirement.part_id not like 'X2%' or requirement.part_id='X2B1' or requirement.part_id='X2R1' or requirement.part_id='X22')

Here is the query to create all items on a master from the sub masters it includes:

insert into requirement
(workorder_type, workorder_base_id, workorder_lot_id, workorder_split_id, workorder_sub_id, operation_seq_no, piece_no, subord_wo_sub_id,
part_id,reference,status,qty_per,qty_per_type,fixed_qty,scrap_percent,dimensions,dim_expression,usage_um,effective_date,discontinue_date,calc_qty,issued_qty,
required_date,close_date,unit_material_cost,unit_labor_cost,unit_burden_cost,unit_service_cost,burden_percent,burden_per_unit,fixed_cost,
drawing_id,drawing_rev_no,vendor_id,vendor_part_id,est_material_cost,est_labor_cost,est_burden_cost,est_service_cost,rem_material_cost,
rem_labor_cost,rem_burden_cost,rem_service_cost,act_material_cost,act_labor_cost,act_burden_cost,act_service_cost,mfg_name,mfg_part_id,
protect_cost,length,width,height,drawing_file,warehouse_id,allocated_qty,fulfilled_qty,planning_leadtime,required_for_setup,user_1,user_2,
user_3,user_4,user_5,user_6,user_8,user_9,user_10,udf_layout_id,calc_fixed_scrap,wbs_code,wbs_description,wbs_clin,wbs_customer_wbs,wbs_tdc,
inherit_warehouse,location_id,due_date,dispatched,orig_stage_revision_id)
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,--r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4),--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2),--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2), --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and --this code only works for lot/split/sub of 0/0/0
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--the rest of this query is only needed if you want to exclude certain parts from the update
and r.part_id not like 'X7%'
and (r.part_id not like 'X1%' or r.part_id='X1B1' or r.part_id='X1R1')
and (r.part_id not like 'X2%' or r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22')

If you opt to exclude parts from your update you will need this query, it will tell you if your insert will fail due to operations having conflicting numbers. You will need to match the operation number to the one used on the sub master (or one not used) before you can run the insert.
Query to find parts that conflict on operation number: (this query could be cleaner, but it works)

select 
allPartsRemaining.workorder_type,
allPartsRemaining.workorder_base_id,
allPartsRemaining.workorder_lot_id,
allPartsRemaining.workorder_split_id,
allPartsRemaining.workorder_sub_id,
allPartsRemaining.operation_seq_no,
allPartsRemaining.piece_no,
allPartsRemaining.part_id,
allPartsForInsert.part_id,
allPartsForInsert.workorder_type
from 
(
select requirement.* from (
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
where requirement.workorder_type is not null
) as allPartsRemaining 
join 
(
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id as r_subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4) as one,--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2) as two,--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2) as three, --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--Update this list of ignored parts with your rules: (in this case ignored parts need to be included), delete them if you're not excluding parts
and r.part_id like 'X7%'
and (r.part_id like 'X1%' and not (r.part_id='X1B1' or r.part_id='X1R1'))
and (r.part_id like 'X2%' and not (r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22'))
)as allPartsForInsert on 
allPartsRemaining.workorder_type=allPartsForInsert.workorder_type and
allPartsRemaining.workorder_base_id =allPartsForInsert.workorder_base_id and
allPartsRemaining.workorder_lot_id=allPartsForInsert.workorder_lot_id and
allPartsRemaining.workorder_split_id=allPartsForInsert.workorder_split_id and
allPartsRemaining.workorder_sub_id=allPartsForInsert.subord_wo_sub_id and
allPartsRemaining.operation_seq_no=allPartsForInsert.operation_seq_no and
allPartsRemaining.piece_no=allPartsForInsert.piece_no and
allPartsRemaining.part_id  allPartsForInsert.part_id --It doesn't matter if they share the same part_id, they are being ignored or updated based on part id so having the same part id is good.

Here’s one last query, not strictly needed, but you may find you want it:
Query to take the rows we’re deleting, the rows we’re inserting and determine what the difference is: (once everything is run this should return nothing)

select existingRows.*, newRows.* from 
(
select requirement.*
from (
select 
*
from requirement 
where requirement.workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left join requirement on
	engMasterRequirements.workorder_type = requirement.workorder_type and
	engMasterRequirements.workorder_base_id = requirement.workorder_base_id and
	engMasterRequirements.workorder_lot_id = requirement.workorder_lot_id and
	engMasterRequirements.workorder_split_id = requirement.workorder_split_id and
	engMasterRequirements.subord_wo_sub_id = requirement.workorder_sub_id
--Not needed if you're not excluding parts:
where requirement.part_id not like 'X7%'
and (requirement.part_id not like 'X1%' or requirement.part_id='X1B1' or requirement.part_id='X1R1')
and (requirement.part_id not like 'X2%' or requirement.part_id='X2B1' or requirement.part_id='X2R1' or requirement.part_id='X22')
) as existingRows

left join (
select
r.workorder_type,
engMasterRequirements.workorder_base_id,
--r.workorder_base_id,
r.workorder_lot_id,r.workorder_split_id,
engMasterRequirements.subord_wo_sub_id,
r.operation_seq_no,
r.piece_no,
r.subord_wo_sub_id as r_subord_wo_sub_id,
r.part_id,r.reference,r.status,
r.qty_per,
r.qty_per_type,r.fixed_qty,r.scrap_percent,r.dimensions,r.dim_expression,
r.usage_um,
r.effective_date,r.discontinue_date,
round((engMasterRequirements.qty_per * r.calc_qty)+.00004,4) as one,--The data in the database suggests Visual rounds this result up
r.issued_qty,
r.required_date,r.close_date,
r.unit_material_cost,
r.unit_labor_cost,r.unit_burden_cost,r.unit_service_cost,r.burden_percent,r.burden_per_unit,r.fixed_cost,
r.drawing_id,r.drawing_rev_no,
r.vendor_id,
r.vendor_part_id,
round((r.est_material_cost * engMasterRequirements.qty_per),2) as two,--The data in the db suggests Visual uses normal rounding on this 
r.est_labor_cost,r.est_burden_cost,r.est_service_cost,
round((r.rem_material_cost * engMasterRequirements.qty_per),2) as three, --The data in the db suggests Visual uses normal rounding on this 
r.rem_labor_cost,r.rem_burden_cost,r.rem_service_cost,r.act_material_cost,r.act_labor_cost,r.act_burden_cost,r.act_service_cost,r.mfg_name,r.mfg_part_id,
r.protect_cost,r.length,r.width,r.height,r.drawing_file,r.warehouse_id,r.allocated_qty,r.fulfilled_qty,r.planning_leadtime,r.required_for_setup,r.user_1,r.user_2,
r.user_3,r.user_4,r.user_5,r.user_6,r.user_8,r.user_9,r.user_10,r.udf_layout_id,r.calc_fixed_scrap,r.wbs_code,r.wbs_description,r.wbs_clin,r.wbs_customer_wbs,r.wbs_tdc,
r.inherit_warehouse,r.location_id,r.due_date,r.dispatched,r.orig_stage_revision_id
from
(
select 
*
from requirement 
where workorder_type='M' and 
subord_wo_sub_id is not null-- needed to only get masters that were included on other masters
) as engMasterRequirements
left outer join requirement as r on
	r.workorder_type = 'M' and
	r.workorder_base_id = engMasterRequirements.part_id and
	r.workorder_lot_id = 0 and
	r.workorder_split_id = 0 and
	r.workorder_sub_id = 0
where r.workorder_type is not null--it's possible to get sub items for where the engineering template could be deleted, exclude them
--Not needed if you're not excluding parts:
and r.part_id not like 'X7%'
and (r.part_id not like 'X1%' or r.part_id='X1B1' or r.part_id='X1R')
and (r.part_id not like 'X2%' or r.part_id='X2B1' or r.part_id='X2R1' or r.part_id='X22')
) as newRows on
	existingRows.workorder_type = newRows.workorder_type and
	existingRows.workorder_base_id = newRows.workorder_base_id and
	existingRows.workorder_lot_id = newRows.workorder_lot_id and
	existingRows.workorder_split_id = newRows.workorder_split_id and
	existingRows.workorder_sub_id = newRows.subord_wo_sub_id and
	existingRows.operation_seq_no = newRows.operation_seq_no and
	existingRows.piece_no = newRows.piece_no 
where existingRows.workorder_type is null or newRows.workorder_type is null

Leave a comment

Copy Data from Excel to SQL Server (or any DBMS) Script to generate Insert Statements in Excel

Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)

That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.

Sub GenerateInsertRecords()
'Code thanks to https://anthonystechblog.wordpress.com
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
    Dim i As Integer, ii As Double, iColumn as integer
    Dim str As String, strInsert As String, strComplete As String
    str = ""
    str = "insert into TABLE_NAME_HERE ("
    For i = 1 To 999
        If Len(Cells(1, i)) < 1 Then Exit For
        str = str & Cells(1, i) & ","
    Next i
    str = Left(str, Len(str) - 1)
    str = str & ") values ("
    
    Dim oConvert As Variant
    strInsert = str
    strComplete = ""
    For ii = 2 To 65000
        If Len(Cells(ii, 1)) < 1 Then Exit For
        strComplete = strComplete & strInsert
        str = ""
        For iColumn = 1 To i - 1
            oConvert = getInput(Cells(ii, i))
            str = str & oConvert & ","
        Next iColumn
        str = Left(str, Len(str) - 1)
        str = str & ")"
        strComplete = strComplete & str & vbNewLine
    Next ii
    
    Dim oFS As Scripting.FileSystemObject
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Dim oText As Scripting.TextStream
    Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True)
    oText.Write (strComplete)
    oText.Close
    MsgBox ("complete")
End Sub

Function getInput(theInput As Variant) As Variant
    Dim retval As Variant
    If IsNull(theInput) Then
        retval = "Null"
    ElseIf UCase(theInput) = "NULL" Then
        retval = "Null"
    ElseIf IsNumeric(theInput) Then
        retval = theInput
    Else
        retval = "'" & theInput & "'"
    End If
    getInput = retval
End Function

2 Comments