passing data from a select to an insert 1

[fraxx]

I work as a storage technician, and I'm looking for ways to automate some of the tasks. SQL development is not in my job description, be gentle.

I have tried to model a database following the procedures and layout of a storage array.

One storage Array is identied with it's serial number. There are no two arrays with the same serial.
An array can have several Ports. A port has a name, and one array cannot have two ports with the same name.

A port can have several HostGroups. A hostgroup has a name, and one port cannot have two hostgroups with the same name. However, another port can (and will) have a hostgroup with the same name.

An array has several PhysicalHosts. A PhysicalHost has a name, and this name is unique. An array cannot have two PhysicalHosts with the same name.


Contrary to what you may think, a HostGroup is not a group of hosts. It's more the representation of a PhysicalHost on a particular port. Typically, a HostGroup is created on two ports for redundancy, ie there are two HostGroups with the same name on a pair of ports.
Therefore, standard operating procedure dictates that the name of the HostGroup is the same as the PhysicalHost. All HostGroups with the same name belong to the same PhysicalHost.

All tables have an ID column with the uniqueidentifier datatype. This is set as rowid with newid() as default.

I have modeled the relations using the ID column.

I also have a RawData table, containing the current configuration from the storage array. This data is populated by using bulk import from a csv file the management application outputs. I decided to not preprocess this file, I just import it as is and use select statements to get what I want.

The RawData table will have many redundancies, what I have described above is only a subset of the actual information I'm trying to process and there are reasons for the apparent redundancies.

By using the following select statement I can get the existing Array-Port-HostGroup combinations:

SELECT DISTINCT SerialNumber
,PortDisplayName
,Hostgroupname
FROM RawData
WHERE SerialNumber IS NOT NULL AND
PortDisplayName IS NOT NULL AND
Hostgroupname IS NOT NULL


Using similar statements and just using manual inserts, I have populated the Array, Port and PhysicalHost tables.

In practice, I may at most have 3 or 4 arrays to manage. Each array has maybe 24 ports. This information is virtually static as well.

However, I would like to automate the HostGroup inserts.

This statement does what I want:

INSERT INTO HostGroup
(rPhysicalHost
,rPort
,Name)
SELECT
(SELECT PhysicalHost.PhysicalHostID
FROM PhysicalHost
WHERE PhysicalHost.Name = 'MyHostNameA'),
(SELECT Port.PortID
FROM Port
WHERE Port.Name = 'MyPort0' AND Port.rArray =
(SELECT Array.ArrayID
FROM Array
WHERE Array.serial = '123456789')
)
,'MyHostNameA'


In this example, MyHostNameA is both the physical host name being referenced, and the name of the hostgroup I am inserting. This may not always be the case, depending on the operating procedures.

Now, to the actual question:
How can I take the output from the select statements where I get the Array-Port-HostGroup combination, and pass this to Insert statement to populate the HostGroups table?

 

[gmmastros]

The problem you are having occurs because you are using sub-queries. When you were writing this query, you probably got an error like, "Sub query returned more than 1 value" (or something like that).

You can insert data in to another table where multiples rows are inserted with one query. If I could, I would show you how to do this. Unfortunately, you have not provided enough information for me to do this.

Specifically, I would need to know how the tables are joined together. Your query involves 4 tables. One of them is the table you are inserting in to. We can ignore that for now. Of particular interest is the other 3 tables (PhysicalHost, Port, and Array). These 3 tables are probably joined somehow, but the details of the join are not shown in the code you have provided.

My question to you is, how are the tables joined? Specifically, there should be a column in the PhysicalHost table that mimics a column in the other table(s). This is common in relational databases.

If this is all very confusing to you, then I recommend you run this...

Select Table_Name, Column_Name
From   Information_Schema.Columns
Where  Table_Name In ('PhysicalHost','Port','Array')
Order By Table_Name, Ordinal_Position

Post the output here and we'll be able to help more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fraxx]

I've left work for today, but the insert statements actually works. I don't get an error, although I did get the errors you mentioned until I got it right. It does what I want. However, I obviously don't want to type in the values manually, I want to use the output from the select statement with the triplet output and pass it as parameters to the insert.

I'll post more details tomorrow.

 

[gmmastros]

I don't get an error, although I did get the errors you mentioned until I got it right.

That's exactly what I was trying to say.

And, like I mentioned before, it's the sub-queries that are causing your problem. You see, you WANT to return multiple values (by removing the where clause conditions). But, since you are using a sub-query approach, this doesn't work.

My suggestion is to first create a query that represents all of the data you would like to put in to the target table (HostGroup). Construct this query in such a way that it returns all the data you want. It's important that the ordering of the columns is correct too.

Before I show you some code, let me explain what I mean by sub-query. If you already know this, then just ignore me (I won't be offended).

Your existing code:

INSERT INTO HostGroup
           (rPhysicalHost
           ,rPort
           ,Name)
SELECT
    [green]([/green]
      SELECT PhysicalHost.PhysicalHostID
      FROM PhysicalHost
      WHERE PhysicalHost.Name = 'MyHostNameA'
    [green])[/green],
    [!]([/!]
      SELECT Port.PortID
      FROM Port
      WHERE Port.Name = 'MyPort0' 
            AND Port.rArray =
              [blue]([/blue]
                 SELECT Array.ArrayID
                 FROM Array
                 WHERE Array.serial = '123456789'
              [blue])[/blue]
    [!])[/!]
    ,'MyHostNameA'

The part inside the green parenthesis is a sub-query. Likewise, the part in the red and blue are also sub-queries. Sub-queries have a limitation in that they can only return a single value.

Instead, if you can write a query that Join's the tables together, you can return multiple rows. Those multiple rows can then be inserted in to the target table.

Like I said before, I don't know how the tables are joined. As such, the query I show below may not run in your database. It's the 'concept' that is important anyway.

For example:

Select PhysicalHost.PhysicalHostId,
       Port.PortId, 
       PhysicalHost.Name
From   PhysicalHost
       Inner Join Port
         On [!]PhysicalHost.PortId = Port.PortId[/!]
       Inner Join Array
         On [!]Port.rArray = Array.ArrayId[/!]

Now, understand that I made some assumptions here. Specifically, I assume that the tables are joined in a certain way. Column Names may not be right, but hopefully you get the idea.

Once you have the query returning the correct data, all you need to do to insert into another table is to add the Insert Into line, like this...

INSERT INTO HostGroup
           (rPhysicalHost
           ,rPort
           ,Name)
Select PhysicalHost.PhysicalHostId,
       Port.PortId, 
       PhysicalHost.Name
From   PhysicalHost
       Inner Join Port
         On PhysicalHost.PortId = Port.PortId
       Inner Join Array
         On Port.rArray = Array.ArrayId

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

George,
Aren't you a teacher?
I learned from you more than I ever learned from BOL and other sources.
I wish somebody like you wrote all MS Help files.
THANK YOU!!!!
Not for that particular post, but for all your efforts here!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Thanks Boris. It's nice to hear that once in a while. I've learned a few things from you too.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fraxx]

Thank you for you kind assistance gmmastros. I think I'm starting to understand the concept of joins. In reality, I think I would have a long time ahead of me using your outlined approach getting my head around it.

I solved the problem by using a cursor and stepping through the insert. The entire run took less than a second, and this was with a production set of data.

 

[gmmastros]

fraxx,

Understanding the concept of joins is the single most important concept you should learn if you want to be successful dealing with a database. Cursors are notorious for being slow. It's pretty cool that your cursor code ran in less than a second. You see, with small datasets, any old method will be slow. As the size of the data increases, you will find that cursors are MANY times slower than set based operations.

For example, suppose you wanted to spread a pile of gravel on your driveway. There are several methods you could use to accomplish this...

1. You could pick up a handful of gravel at a time and toss it about until you have an even layer of gravel.
2. You could use a shovel to spread the gravel.
3. You could use a plow attached to your riding lawn mower.

If your driveway is small (just big enough to fit one car), each approach would not take very long to accomplish. But, if your driveway was long and wide enough to accommodate multiple cars, the plow method would be many times faster.

It's the same idea with cursors. Cursors would be the slowest method (picking up each individual stone and tossing it about).

Seriously! It's that bad. You need to learn joins. It's that important.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom