January 19, 2018 at 9:16 pm
I would like to join 2 tables in a view, but be able to insert into said view and have the record only be written to one of the tables (i.e. the one with the higher set of auto-incrementing IDs).
For example: Table A contains 100 records, with IDs of 1-100, Table B contains 100 records with IDs of 101 -150. I would like to create a view that unions both, then when inserting into the view, the record actually gets inserted into table B.
Any ideas would be appreciated. Thanks all.
January 20, 2018 at 5:07 am
You'll need to use an INSTEAD OF trigger. For example:
USE Sandbox;
GO
CREATE TABLE Table1 (ID int, String varchar(10));
CREATE TABLE Table2 (ID int, String varchar(10));
INSERT INTO Table1
VALUES (1,'ABC'),
(2,'ABC'),
(3,'ABC'),
(4,'ABC'),
(5,'ABC');
INSERT INTO Table2
VALUES (6,'DEF'),
(7,'DEF'),
(8,'DEF'),
(9,'DEF'),
(10,'DEF');
GO
CREATE VIEW Table1And2 AS
SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2;
GO
SELECT *
FROM Table1And2;
GO
CREATE TRIGGER GoToTable2 ON Table1And2
INSTEAD OF INSERT
AS
INSERT INTO Table2 (ID, String)
SELECT i.ID,I.String
FROM inserted i;
GO
INSERT INTO Table1And2
VALUES (11,'GHI');
SELECT *
FROM Table1And2;
SELECT *
FROM Table1;
SELECT *
FROM Table2;
GO
DROP VIEW Table1And2;
DROP TABLE Table2;
DROP TABLE Table1;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 29, 2018 at 11:11 am
Can you put a check constraint on both tables, e.g. ID <= 100. This can give you a "partitioned view". It works for =, but I have never tried it for <=.
https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx
January 29, 2018 at 11:56 am
Ed B - Monday, January 29, 2018 11:11 AMCan you put a check constraint on both tables, e.g. ID <= 100. This can give you a "partitioned view". It works for =, but I have never tried it for <=.https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx
The check constraint is the key. It should work for whatever range the check contraint dictates. Note that even if you're using an identity key with range management, you still need to duplicate that constraint w/ a check constraint to use the view for CRUD.
This article helped me to implement such an approach: https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply