Topic: General SQL Stuff (Read 4050 times)

ivan · « **on:** March 22, 2007, 02:45:15 PM »

Someone gives you a flat tab delimited text file, and wants you to beat it against a SQL database and append a column or two.

How do YOU do it?

ivan · « **Reply #1 on:** March 22, 2007, 02:46:45 PM »

This is just to get the ball rolling. I could probably think of something more interesting later on.

pbsaurus · « **Reply #2 on:** March 22, 2007, 03:00:51 PM »

It depends.

TheJudge · « **Reply #3 on:** March 22, 2007, 03:01:47 PM »

Start by importing your flat file in SQL.

In Enterprise Manager (or SQL server management studio if you are on 2005), use the "import data" function using flat file as a source, specifying that it's a tab delimited file.

Now you can manipulate everything in SQL, make the changes you require, and extract back into a tab delimited file, or CSV.

BizB · « **Reply #4 on:** March 22, 2007, 03:05:27 PM »

First thought: I would use Excel to either build the sql statements or to connect to the db and stuff the data in there.

However, before voicing this solution, I'd engage the person in a conversation that would include the following.
Why do you need this?
How many records?
How many columns?
How many records are in the table already?
Do you need this data to become a permanent part of the records, or would a temporary table with like-keys be sufficient?

The first question is key. I couldn't tell you how many times that question alone has moved something off of my plate, entirely.

Pyrenus · « **Reply #5 on:** March 22, 2007, 03:41:26 PM »

Bulk insert the file into a new table in your database.
Add the new columns, do all your manipulations
Open Query Analyzer and select the rows to export
Save to tab delimitted text file.
Delete the table you created
Spend the next hour reading forums while "working on that damn report"

Pyrenus · « **Reply #6 on:** March 22, 2007, 03:43:02 PM »

Report I had to write today..

You have two tables:

Orders
OrderID INT NN PK

Order_Items
OrderItemID INT NN PK
OrderItemOrderID INT NN FK TO orders.OrderID
OrderItemType Is either Kitty, Doggy, or Puppy

An order must contain at least one item, and an item is one of 3 types.

You need to generate a report of orders based on 3 parameters.
HasAKitty, HasADoggy, or HasAPuppy

Each parameter is a bit, 1 or 0.

When HasAKitty = 1, HasADoggy = 1, and HasAPuppy = 1, your report should return all orders, since each order will contain at least one of those items.

If HasAKitty = 0, HasADoggy = 1, and HasAPuppy = 1, then you should return all orders that contain EITHER a Doggy or a Puppy. Those orders may contain Kitties, but we don't care.

If HasAKitty = 0, HasADoggy = 0, HasAPuppy = 1, Return all orders that contain at least 1 puppy. Other items in the order don't matter. ETC.

Now, my problem was to write one SP that uses one query to get the correct results based on any combination of input parameters.

SELECT OrderID FROM Orders
....

ivan · « **Reply #7 on:** March 22, 2007, 04:10:31 PM »

Quote from: ivan on March 22, 2007, 03:58:08 PM

So did you find a boolean statement that does the trick?

I would've probably settled for a UNION juke.

select distinct OrderID from Order_Items where OrderItemType = Kitty and @HasKitty = 1
union
select distinct OrderID from Order_Items where OrderItemType = Doggy and @HasDoggy = 1
union
select distinct OrderID from Order_Items where OrderItemType = Puppy and @HasPuppy = 1

Pyrenus · « **Reply #8 on:** March 22, 2007, 04:19:40 PM »

That would have worked. I don't use UNION and I rarely ever think to use it.

Here's the solution I came up with:

select orders.* from orders
LEFT OUTER JOIN order_items P ON orderID = orderID AND type = PUPPY
LEFT OUTER JOIN order_items D ON orderID = OrderID AND type = Doggy
LEFT OUTER JOIN order_items K ON orderID = orderID AND type = Kitty
WHERE ISNULL(P.ID, 0) =
CASE
WHEN @HasKitty = 1 THEN ISNULL(P.ID, 1)
ELSE -1
END
AND ISNULL(D.ID, 0) =
CASE
WHEN @HasDoggy = 1 THEN ISNULL(D.ID, 1)
ELSE -1
END
AND ISNULL(K.ID, 0) =
CASE
WHEN @HasKitty = 1 THEN ISNULL(K.ID, 1)
ELSE -1
END

ivan · « **Reply #9 on:** March 22, 2007, 04:29:13 PM »

Try it. Sometimes it runs a lot faster than joins with boolean conditions.

ivan · « **Reply #10 on:** March 22, 2007, 04:38:01 PM »

Not that there's anything wrong with your (very clever) solution -- it may work better than unions in some situations.

Pyrenus · « **Reply #11 on:** March 22, 2007, 04:44:01 PM »

Believe me, I'd rather be corrected if there is a better way of doing it.

I tried both and your solution ran 1 millisecond faster. Both execution plans appeared very similar, and running a trace showed both queries consuming 0 units of CPU and 0 duration.

I need a slower server to get a better comparison.

ivan · « **Reply #12 on:** March 22, 2007, 05:13:07 PM »

Or like 1 billion Kitties, Doggies and Puppies.

Vespertine · « **Reply #13 on:** March 23, 2007, 12:55:23 PM »

Quote from: Pyrenus on March 22, 2007, 04:19:40 PM

That would have worked. I don't use UNION and I rarely ever think to use it.

Here's the solution I came up with:

select orders.* from orders
LEFT OUTER JOIN order_items P ON orderID = orderID AND type = PUPPY
LEFT OUTER JOIN order_items D ON orderID = OrderID AND type = Doggy
LEFT OUTER JOIN order_items K ON orderID = orderID AND type = Kitty
WHERE ISNULL(P.ID, 0) =
CASE
WHEN @HasKitty = 1 THEN ISNULL(P.ID, 1)
ELSE -1
END
AND ISNULL(D.ID, 0) =
CASE
WHEN @HasDoggy = 1 THEN ISNULL(D.ID, 1)
ELSE -1
END
AND ISNULL(K.ID, 0) =
CASE
WHEN @HasKitty = 1 THEN ISNULL(K.ID, 1)
ELSE -1
END

I have a question. Your description of the data relationships says that every order will have at least 1 item. As such, why are you using left outer joins. If your description is accurate, INNER joins will always return you data.

ivan · « **Reply #14 on:** March 23, 2007, 02:15:35 PM »

Quote from: TheJudge on March 22, 2007, 03:01:47 PM

Start by importing your flat file in SQL.

In Enterprise Manager (or SQL server management studio if you are on 2005), use the "import data" function using flat file as a source, specifying that it's a tab delimited file.

Now you can manipulate everything in SQL, make the changes you require, and extract back into a tab delimited file, or CSV.

When I was tossed into the SQL pool 3-4 years ago, this is how I was tought to do it. It's actually a quick process. Usually what I was handed was an Excel sheet, which I would save as a tab-delimited file, then run it through the wizard in Enterprise Manager, use Query Analyzer to build the output, save as text or paste into Excel. A 5-minute job a lot of the time. But something deep inside me chafed at having to use a wizard, having to create files that are used once, and having to do so many steps even if they are quick. "There's gotta be a more elegant way to do this," I kept repeating. But my biggest qualm is: I don't like using Enterprise Manager for routine little jobs. It's sort of like using an ore carrier to bring home a carton of milk. My second biggest qualm is: I don't like doing routine little jobs in the first place.

Quote from: BizB on March 22, 2007, 03:05:27 PM

First thought: I would use Excel to either build the sql statements or to connect to the db and stuff the data in there.

However, before voicing this solution, I'd engage the person in a conversation that would include the following.
Why do you need this?
How many records?
How many columns?
How many records are in the table already?
Do you need this data to become a permanent part of the records, or would a temporary table with like-keys be sufficient?

The first question is key. I couldn't tell you how many times that question alone has moved something off of my plate, entirely.

The first part of this solution addresses my biggest qualm: instead of using Enterprise Manager/Query Analyzer, use a client with a light footstep. Excel would do quite nicely. The second part of this solution addresses my second biggest qualm head on. I like how BizB thinks; but that's no surprise.

Quote from: Pyrenus on March 22, 2007, 03:41:26 PM

Bulk insert the file into a new table in your database.
Add the new columns, do all your manipulations
Open Query Analyzer and select the rows to export
Save to tab delimitted text file.
Delete the table you created
Spend the next hour reading forums while "working on that damn report"

I just started fooling around with bcp recently, and will be eliminating a slew of DTS packages (another peeve) whose only purpose is to generate text files. The problem with bcp is that you need SQL2000 or higher, and only some of our servers have been upgraded.

So here is what I did:

I wrote a Tool in VB6 that allows people to open an Excel sheet (loads it into a flex-grid), add columns selected from a list, and save it as a new Excel sheet. The app connects to SQL, matches up SKUs on the sheet with SKUs in the database, then builds queries based on selected data items. So when someone sends me an Excel sheet with the request "could you add the latest prices and availability to this list," I send it back with the curt reply: "Use The Tool."

pbsaurus · « **Reply #15 on:** March 23, 2007, 02:46:53 PM »

Yep, it's best to teach them how to fish.

Pyrenus · « **Reply #16 on:** March 26, 2007, 12:34:35 PM »

Quote from: Vespertine on March 23, 2007, 12:55:23 PM

I have a question. Your description of the data relationships says that every order will have at least 1 item. As such, why are you using left outer joins. If your description is accurate, INNER joins will always return you data.

Each order will have at least 1 item, but not all 3 types. If I used inner joins the query would only return orders that had at least 1 of each type of item.

By the way, the ANDS should be ORs in that query I posted.

News:

Recent Forum Posts

Shout Box

Author Topic: General SQL Stuff (Read 4050 times)