Archive for category SQL

SQL Server – Get data between two dates

This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:

select * from
tableName
where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and
datediff(day,'2011-06-28 12:28:34.480',theDate)<=0

The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:

select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')

It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.

If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.

For SQL Server 2000:

select * from
tableName
where datediff(day,?,theDate)>=0 and
datediff(day,?,theDate)<=0

For SQL Server 2005+:

select * from
tableName
where datediff(day,@StartDate,theDate)>=0 and
datediff(day,@EndDate,theDate)<=0

Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).

Leave a comment

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

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

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

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

The Curse and Blessings of Dynamic SQL

Here’s a topic I wanted to post on for some while. I’ve worked with many developers in my career and the topic of dynamic SQL has been a recurring theme. Should you use it? Isn’t there security / speed issues if you use it? How do you use it well? As with many topics it depends what your application for it is. Today I stumbled on an article that covers the topic very well. If you are interested in getting more info be sure to let me know. Query design is something I’ve noticed many developers could use some improvement on.

http://www.sommarskog.se/dynamic_sql.html

Leave a comment