May 21, 2009 at 11:02 am
Hello all, this is my first post though I have been a memeber for a while and you guys have saved my butt many times and taught me a bunch. Thanks!
I am a rookie, though I have been the DB "admin" at my company for almost 7 years! I just got Management Studio Express, before this I did everything in Access ADP.
I have several SQL tables that contain similarly structured data for two deparments in our company. I want to segregate the data into two seperate DB's. The table names/sturctures need to stay the same in each DB because we have a whole pile of tools that work based on this data and it is hard coded by our vendor. I need the useres of each DB to see all of the data, but only edit and add data that resides in thier DB. Using UNION I am albe to achieve the appropriate view of the data across the two DB's and my tools work great... until I try to create or edit data. UNION doesn't allow this I have found.
I need the view that the UNION creates and the ability to have users edit/create. The tools we use will point at the appropriate DB for the users. Can I create some sort of JOIN that shows all of the data but only edits/creates data in the primary DB?
Here is my code:
SELECT *
FROM dbo.PED
UNION
SELECT *
FROM DB2.dbo.PED
Thanks for all of your help in advance,
Jeremiah
-------------------------
Jeremiah M
www.goponderosa.com
May 21, 2009 at 12:55 pm
Hi and welcome to the forum.
Yes a cross-database union or join is possible, however you should include some kind of flag in the results so you know which DB it comes from
SELECT 'DB1' AS DB, * FROM DB1.dbo.PED
UNION
SELECT 'DB2' AS DB, * FROM DB2.dbo.PED
Then when you display to the user, you can mark the DB1s or DB2s as editable, and only update or insert those.
I would suggest keeping all the data in one database and adding a department column, but if you are constrained by a software vendor, then perhaps that isn't possible.
Does the vendor software demand a select statement - then it works out its own update/insert from that, and it allows you to put your union into that, but of course it can't work out the update/inserts?
May 21, 2009 at 1:11 pm
Thanks for the response Tom.
The reason for the split is we are tracking two very different technologies in the two departments and management of the tables is a nightmare, currently our main table has ~1,000,000 records and will double or triple soon. Also, one department is all manual entry while the other is being fed by SNMP calls to hardware. Neither dept. wants to be responsible for toasting the others data, but we all use the same tools that put the information together to give us a full picture of our telephone network.
I will look into your suggestion and see if I can get the result I need.
Jeremiah
-------------------------
Jeremiah M
www.goponderosa.com
May 21, 2009 at 1:18 pm
A view with Union All in it can allow for edits to the underlying table data, IF there is a key in the two tables that indicates precisely which table each piece of data came from.
On the other hand, the better way to do this is to have the data edits be through stored procedures, and the procs can have logic in them to know which table to edit in which database. That's MUCH more secure, more efficient, easier to maintain and work with, etc.
If you want to go with the first option, what you need to do is add a column to each table that identifies which table it is, and a check constraint on each table that makes sure the values can't overlap. The simplest example of this would be a "DBName" column, and a check constraint that forces the value to be "DB1" or "MyFirstDatabase" or whatever. You'll probably also want a default constraint on the column, with the same value.
Here's a sample of how that works:
set nocount on;
go
if object_id(N'dbo.T1andT2') is not null
drop view dbo.T1andT2;
if object_id(N'dbo.T1') is not null
drop table dbo.T1;
if object_id(N'dbo.T2') is not null
drop table dbo.T2;
go
create table dbo.T1 (
TableName char(10) not null default ('T1'),
constraint CK_TableNameT1 check (TableName = 'T1'),
ID int identity,
constraint PK_T1 primary key (TableName, ID),
Col1 varchar(100));
go
create table dbo.T2 (
TableName char(10) not null default ('T2'),
constraint CK_TableNameT2 check (TableName = 'T2'),
ID int identity,
constraint PK_T2 primary key (TableName, ID),
Col1 varchar(100));
go
create view dbo.T1andT2
as
select TableName, ID, Col1
from T1
union all
select TableName, ID, Col1
from T2;
go
insert into dbo.T1 (Col1)
select 'A';
insert into dbo.T2 (Col1)
select 'B';
select *
from dbo.T1andT2;
update dbo.T1andT2
set Col1 = 'C'
where ID = 1
and TableName = 'T1';
select *
from dbo.T1andT2;
There are specifics in the chapter on "Create View" in Books Online.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2009 at 1:20 pm
Here's another example. In this one, I set the ID column of one of the tables to be negative 1 with an increment of minus 1, so I can use an update command that just uses the ID column in its Where clause:
set nocount on;
go
if object_id(N'dbo.T1andT2') is not null
drop view dbo.T1andT2;
if object_id(N'dbo.T1') is not null
drop table dbo.T1;
if object_id(N'dbo.T2') is not null
drop table dbo.T2;
go
create table dbo.T1 (
TableName char(10) not null default ('T1'),
constraint CK_TableNameT1 check (TableName = 'T1'),
ID int identity (-1,-1),
constraint PK_T1 primary key (TableName, ID),
Col1 varchar(100));
go
create table dbo.T2 (
TableName char(10) not null default ('T2'),
constraint CK_TableNameT2 check (TableName = 'T2'),
ID int identity,
constraint PK_T2 primary key (TableName, ID),
Col1 varchar(100));
go
create view dbo.T1andT2
as
select TableName, ID, Col1
from T1
union all
select TableName, ID, Col1
from T2;
go
insert into dbo.T1 (Col1)
select 'A';
insert into dbo.T2 (Col1)
select 'B';
update dbo.T1andT2
set Col1 = 'C'
where ID = 1;
select *
from dbo.T1andT2;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2009 at 1:30 pm
Thanks GSquared,
I will try to ingest this and apply it. I will let you know if I have other questions...
Jeremiah
-------------------------
Jeremiah M
www.goponderosa.com
May 22, 2009 at 9:33 am
In order to digest, start with my two examples, then read the bit in Books Online on "Create View". You'll see the elements from that in the examples.
Once you get the basic concept, it's pretty straightforward.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 26, 2009 at 9:48 am
Thanks again GSquared, this is my goal for the day (or week)!
Jeremiah
-------------------------
Jeremiah M
www.goponderosa.com
May 28, 2009 at 3:06 pm
One thought.
If the OP is using SQL Server Express there is a Maximum 4GB database size limitation.
Will this pose a problem in the future if the DB is going to triple in size?
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
May 28, 2009 at 3:09 pm
DougGifford (5/28/2009)
One thought.If the OP is using SQL Server Express there is a Maximum 4GB database size limitation.
Will this pose a problem in the future if the DB is going to triple in size?
One of the ways to "work around" that limitation is to build multiple databases and split the data into them.
I don't recommend that, because (a) it's not precisely ethical to sneak your way around the license, and (b) there are other limitations on Express that matter more than the max database size.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 29, 2009 at 4:31 pm
We are are using SQL Enterprise 2005. Mgt. Studio Express is just for two of our power useres that dont need the full functionality of Enterprise Manager.
Jeremiah
-------------------------
Jeremiah M
www.goponderosa.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply