Editable UNION!

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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

  • Thanks again GSquared, this is my goal for the day (or week)!

    Jeremiah

    -------------------------
    Jeremiah M
    www.goponderosa.com

  • 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"

  • 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

  • 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