Contents tagged with SQL
-
[SQL] Tools I Can't Live Without Right Now
I've been doing loads of SQL crunching lately, staging, versioning, and preparing for a pretty large migration job later this year which involves a great deal of schema and data changes in SQL Server 2005.
A tool which has been saving me a great deal of time is Red Gate's SQL Compare. As the names says, it's able to compare everything you're interested between two databases in when it comes to SPs, tables, functions, roles and so on. You can also generate SQL scripts which wil handle the migration from one version of the database to the other without (if possible) corrupting the data. The tool is dead easy to get into I think - no need to read any manuals or anything, just new, select db1, db2 and compare. Check, uncheck and migrate.
Red Gate also has a SQL Data Compare tool which compares data in two identical databases and helps you move data between them.
If you just want to create a bunch of "INSERT INTO ..." SQL statements from existing tables, perhaps you should look at the stored proc Narayana Vyas Kondreddi has made available from his code library:
This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.
To show you how simple this one is, the easies way to run it is just:
EXEC sp_generate_inserts 'titles'
and out comes a bunch of INSERT statements which you can copy/paste into your batch files. I love small tools like that.
-
[SQL] Finding the Boss with Hierarchical Queries
Never thought I was going to blog about SQL... but I'm doing this mostly for myself because I need to use this at work on monday. I know enough to do database design and whip up a few necessary stored procs, but when it comes to functions and the new Common Table Expression (CTE) in SQL 2005 I'm quite lost. I got a tip about CTE from my buddy Jan-Erik so I sat down with Google and SQL Books online to see if it could help me out.
Most examples out there lists the employees below a manager, and their employess etc. What I needed to do was go the other way - to create a view or something which showed all the employees in a table and who their manager on a certain level is. Not just their immediate boss, but the boss on some upper level.
To test this I started out with a small, simple table called Employee with 4 columns in it (EmpId, Name, BossId, Type), where BossID is the immediate boss, or the "parent":
CREATE TABLE [dbo].[Employee](
TABLE [dbo].[Employee](
[EmpId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[BossId] [int] NULL,
[Type] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
Or something like that J I'm also setting a FK between BossId and EmpId:
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([BossId])
REFERENCES [dbo].[Employee] ([EmpId])
Next I filled it with some data:
1 Helen NULL Mega boss
2 Lisa 1 Boss
3 Otto 1 Boss
4 Carin 2 Team lead
5 Carl 2 Team lead
6 Niel 4 Project manager
7 Thomas 3 Team lead
8 Johan 4 Code monkey
Pretty simple, but enough to test with. This organization has 4 levels, from 'Mega boss' to 'Code monkey'. To iterate "up" in the hierarchy I'm using CTE and the WITH <table(columns)> AS syntax, and I'm putting it into a scalar function which takes the EmpID and the manager Type as parameters:CREATE FUNCTION [dbo].[GetBossByType]
(
@empid int,
@type varchar(20)
)
RETURNS int
AS
BEGIN
DECLARE @bossid int;
WITH reports(EmpId,name,bossid,type) AS
(
-- set the starting point, a specific employee (from in-parameter)
SELECT EmpiD,name,bossid,type FROM Employee WHERE empid = @empid
UNION ALL
-- get the empid from the person (e) who has empid which corresponds the the previous person's (r) bossid
SELECT e.empid,e.name,e.bossid,e.type FROM employee e JOIN reports r
ON r.bossid = e.empid WHERE r.bossid IS NOT NULL
)
-- from the above we have a hierarchical list of managers in a table called "reports",
-- get the id from the boss with the type I'm looking for
SELECT @bossid = empid FROM reports WHERE TYPE = @type;
RETURN @bossid
END
Now, to create a view that has empid, name and boss id for a specific "level" I used a SELECT statement like this:
SELECT EmpId, Name, dbo.GetBossByType(EmpId, 'boss') AS BossId
FROM dbo.Employee
WHERE (BossId IS NOT NULL)
As you can see, I'm calling my function (GetBossByType) as part of the SELECT, passing in the EmpID and the type of manager I want to list. The result of the query/view is this (EmpId, Name, BossId):
2 Lisa 2
3 Otto 3
4 Carin 2
5 Carl 2
6 Niel 2
7 Thomas 3
8 Johan 2
Works for me. Lisa and Otto are of the manager type 'Boss' already, that's why they have their own EmpId as BossId. The hierarchical query can be a bit difficult to grasp first, but it's powerful. Sorry about the bad syntax and not sticking to upper/lower cases everywhere :)
-
[SQL] Using a Variable TOP Clause
This is a simple one, but still worth mentioning. Say you want to select a variable number of rows in a SELECT statement and don't want to use "dynamic SQL" to do that but rather use a SQL Parameter - no problems. In SQL Server 2005 you can type a query like this:SELECT TOP (@number) Title,Body,Author,Created,LastModified FROM Pages ORDER BY LastModified DESC
Note that the parenthesis around @number is important, or you'll end up getting this error:Incorrect syntax near '@number'.
If you're stuck with an older version of SQL Server, there are several ways to work around that problem. You could for example set the ROWCOUNT, as ROWCOUNT supports a variable:
SET ROWCOUNT @number; SELECT Title,Body,Author,Created,LastModified FROM Pages ORDER BY LastModified DESC; SET ROWCOUNT 0