Generating Insert Statements

G’day,

I been asked a few times lately the best way to generate INSERT statements.

I’d always replied to use SSMS.

I know SSMS has this feature, it’s just not something that I have the need to use on a regular basic. And – as we all know – when you don’t use something regularly you tend to forget where to find it, or – as happens reasonably regularly nowadays – the feature, or tool, gets an update and you find yourself re-learning something you thought that you were already familiar with.

Fortunately, the feature to generate INSERT statements was pretty much where I expected – but it did take a few minutes to find the exact spot, so I thought that I’d mention it here – just in case anybody else is looking too, or you just need a reminder that SSMS is very capable of performing this task.

Simply right click on the database that contains the table – or tables – that you need to generate INSERT statements for and select TASKS

Select TASKS

From the resulting menu choose GENERATE SCRIPTS

Select Generate Scripts

Then it’s just a simply case of following the Wizard through.

If you’ve not started this Wizard before then you’ll see the Introduction page – you can safely tick “Don’t show this page again”, so you basically don’t see this page again the next time you start the same Wizard.

You probably don’t want to see this page every time you start the Wizard.

Then, on the next page, either select either all objects to script – or just the specific one(s) that you want. In my case I’m interested in one particular table.

Select the object(s) that you want to script.

On the next page select the location where you want the file to go. I’m selecting a new query editor window, but you could select a file location or the clipboard.

Then hit the “Advanced” button

Selecting the Advanced button is important

Clicking the Advanced button will show the “Advanced Scripting Options” – scroll down until you find “Types of data to script” and select your choice from the drop down. I’ve chose to script “Schema and data” – but you can also select just Data or only schema.

Select the option that best suits your need.

The next page will give you a summary

Hit Next on the summary page

The it’s just a case of hitting Next and then your script is generated. Simply hit finish on the next screen

You’ll see a summary and hit Finish

And you should see your script. Save it to a location of your choice.

Finally – you see your nice INSERT script

I hope this was useful.

Have a great day

Cheers

Marty.

Azure Notebooks – a nice little tool.

Gday,

I’ve been playing with Azure notebooks lately and have found them invaluable as

  • A teaching aid
  • A test sandbox

I’ve been using Python code, and this means that I don’t have to install Python on the machine that I am working at.

It also means that anybody I am demonstrating my code to has only to open a web page that shows my Azure Notebook.

So – how do you get to Azure Notebooks I hear you ask.

Head along to https://notebooks.azure.com and log in with your Microsoft account.

Once there you’ll be able to start a new project

Give in a name, a URL and decide if you want to make this public or not

Create a Project and Share (if you want)

Start the Azure Project up

Start the Project up

Add a Notebook to the project

Add a Notebook

Choose the language, R and Python are available – but also F#

Choose the language for the Azure Notebook.

And then have fun!

I’d really love to see more languages added in the future. These notebooks have potential – on many levels – and they are also FREE!

Have a great day!

Cheers

Marty.

T-SQL Tuesday #109 – Mentors and Mentees

G’day again,

So, it’s time for T-SQL Tuesday again, the blog party 
started by Adam Machanic (b|t) that just keeps on rolling.
I just looked at the rules – and they are quite long, hopefully, I’ve
covered them all – if not, I’m sorry ๐Ÿ™‚
This time we’re talking about people you’re influenced to get
involved in SQL, strengthen their SQL, join the community or in
any other way.
As I regional mentor in the APAC area I often talk to community
members from SQL, Power BI, Power Platform and various other forms
of the Data Platform – Microsoft or otherwise, I’m sure I’ve influenced
a few – but I’d prefer not to name any (as I’ll possibly get that wrong)
But mentors change as interests change and as we drift between
different projects and steer different courses in life – not just work related. Staying in touch and increasing our networks is a vital skill in this day and age.
I’ve sat in many Data Platform classes and events around the world and listened to some of the well know names in our industry.
Your contribution doesn’t have to be big, taking somebody along to a user group is cool, especially one like SQL Social run by Martin Cairney (t) in Melbourne – a seemingly unique format, in a local pub surrounded by friendly members with great stories and offers of help.
Greg Low (b|t) would be one of my all time favourite presenters, mentors and all round friends.
There’s a lot to name – so I won’t, or we’ll be here all day.
One confession is that I may of had a small degree of influence in releasing the hurricane that  is the @TheHybridDBA on to the world – for that, you can judge me!

Have a great mentoring day.

Cheers

Martin.

TSQL Tuesday #108 – Learning to Learn

G’day,

One attribute that I think is more relevant today than ever is to have a broad base of skills with the ability to go deep when needed.

This requires the ability to learn quickly when needed.ย  Which, in turn, requires that we know how to learn quickly.

When I learnt SQL, I had the luxury of time and the ability to simply absorb things slowly, to buy and read books and build a todo list that might take a while to get finished. Those days are fading and a new era of learning is upon us.

Now we are at a point where learning from books (for a lot of things – but not all) is not the best way to go. This isย  more of a function of how long it takes to write and get a book published –ย  often the content is out of date, even at publication time.

Now we are seeing technology changing that fast that evenย  blog posts and other online content is quickly being out dated. This means we have to be vigilant and look at dates associated with online content and judge if this content is of value any longer.

We need to have side projects where we can experiment, we need to network harder than ever and learn form our friends and peers, we need to share our knowledge and work in teams that have great learning cultures and are happy to experiment.

In summary, I feel the biggest skill that is needed today is the ability to learn quickly – and to have a good strategy to accomplish that. To recognise goodย  mentors, have good foresight and look for opportunities to take on projects that will elevate our learning.

Have a great day.

Cheers

Martin

 

 

A lack of foreign keys – a bad culture example.

G’day,

One of the things that I’ve been seeing more often than I would like to lately is large databases with no foreign keys – or minimal foreign keys (and often with those minimal set of keys disabled)

By large databases I’m roughly meaning databases with several hundred tables, and I usually see a lot of these tables with several hundred GB’s of data in them.

When I generally ask about the reason for no foreign key, I’m told

  1. they addย  overhead
  2. they give no benefit
  3. we can’t enter our data properly when we have them

The last one in the above list is generally down to poor modelling – an example being entering a later part of a financial transaction before the first part of the financial transaction has taken place. Once both parts of the financial transaction have taken place then the database is left in a consistent state – BUT, that generally being handled by the app NOT the database – OUCH!

My first though here is “I’m glad I don’t use this company” – and I also wonder why auditing or testing has not picked it up. Quite often it has but somebody, somewhere has decided that there’s a reasonably low chance of that occurring and so no action has been taken – until the ‘bug’ is discovered sometime later and at that point takes a long time – and a lot of money – to fix.

Points 1 and 2 are quoted more often than I would like also – usually stemming from statements that were made by somebody who worked with a long dead version of the database and didn’t quite take the time to understand fully how relational databases work – but who had tremendous influence in the company and a reputation that meant they were not to be crossed. (they have since been proved wrong but have failed to accept it)

These are simply examples of now bad culture can lead to problems years down the road.

Looking at a lot of database that lacked foreign keys, it’s usually pretty simply to find examples of inconsistent data in the tables. – caused by the three issues above.

The moral of the story here is

Always question, always learn and really try to avoid issues that are bound to come back and haunt you.

Always try to build good culture.

Have a great day.

Cheers

Martin.

 

 

 

 

 

Welcome to my new home.

G’day,

I initially set up a blog back in 2010 and wrote semi-regularly until I started back in higher education.

Then, time seemed to be at a premium and I gradually blogged less and less until one day I completely stopped.

Well, now its time to pick my writing up again – and so here we are at a new home

I generally try to syndicate my blog to SQL Server Central – as nearly all of my stuff is data related – generally about the Microsoft Data Platform.

I might bring my historic content across at some stage – but if you are interested then you can find it all on sqlservercentral.com

I hope you might find stuff here interesting.

Have a great day.

Cheers

Martin.