9/20/2023 0 Comments Mysql insert into viewBecause a view in SQL Server is not a physical table and does not hold any data. But, remember that the actual data will be inserted into the underlying table, and a view will just return the data of that underlying table. So, Yes, we can insert data into view in SQL Server. And it does not even exist in the database physically. A view includes a set of SQL queries for retrieving data from the database. In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. Which view does not always allow DML operations through the viewĬan we insert data into view in SQL Server.SQL Server insert into view multiple tables.How to insert a row in a view in SQL Server Management Studio.How to insert a row in a view in SQL Server.Permissions required to insert a row in a view in SQL Server.Restrictions on inserting data through views in SQL Server.Can we insert data into view in SQL Server.INNER JOIN dbo.Table1 T ON DT.TempCol = T. , Table1PK INT NOT NULL CONSTRAINT FK_Table2_Table1_PK FOREIGN KEY REFERENCES dbo.Table1(Table1PK)ĪLTER TABLE dbo.DataTable ADD TempCol INT NOT NULL IDENTITY(1,1) Table2PK INT NOT NULL CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED IDENTITY(1,1) Table1PK INT NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED IDENTITY(1,1) INSERT INTO dbo.DataTable (A, B, C, D, E, F) I assume DataTable does not already have a single unique-field such as an IDENTITY(1,1)? If not, perhaps you should add one that you could use for inserting data into Table1 and Table2.īy way of an example I've created a sample schema, inserted test data into DataTable, modified DataTable to have an IDENTITY(1,1) column, then used that to insert data into both Table1 and Table2: USE tempdb Negating the need for the view and trigger completely, though if this is an operation you will be performing often in your code the view+trigger would still be worth considering to abstract out the need for multiple statements each time.ĬAVEAT: all the above SQL has been typed from thought and not tested, it will need work before there is any guarantee it will work as you need.įrom reading your question, and the comments on the other answers, it seems like you are attempting to fix a problem with DataTable by splitting it into two new tables. JOIN MyTable AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C JOIN inserted AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C Īnd in fact that pair of INSERT statements might work directly as a one-off like so (whether you are using an INT IDENTITY or UNIQUEIDENTIFIER DEFAULT NEWID() type for the key): INSERT table1 (A, B, C) Something like the following might work: CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS This is assuming that you are intending to use a UUID type for your primary key in this case - if you are using an automatically incrementing integer key on table1 there is a little more work to do. (of course what rows are output when you SELECT from the view is unimportant if you don't intend to SELECT from it and it only exists to provide a template to INSERT into for the trigger to do its magic) SELECT ISNULL(table1.ID, table2.ID), A, B, C, D, E, FįULL OUTER JOIN table2 ON table1.ID = table2.ID Or if there might be rows in each table without matching rows in the other: CREATE VIEW newView AS INSERT table2 SELECT ID, D, E, F FROM insertedĪssuming the view is something like: CREATE VIEW newView AS INSERT table1 SELECT ID, A, B, C FROM inserted it is part of the application logic and not a one-off data transformation exercise) then you could use a view onto Table1 and Table2 with an INSTEAD OF INSERT trigger to manage splitting the data (and arranging the keys/relationships) - then you would just do: INSERT newView SELECT NEWID(), A, B, C, D, E, F FROM MyTableĪnd the trigger could be as simple as: CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS If this is something you are planning to do regularly (i.e.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |