Localhornygirls - Sql server updating data in a view

In SQL, a view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table.The fields in a view are fields from one or more real tables in the database.

An updatable view is one which allows performing an UPDATE command on itself without affecting any other table. The view is defined based on one and only one table. The view must include the PRIMARY KEY of the table based upon which the view has been created. The view should not have any field made out of aggregate functions. The view must not have any DISTINCT clause in it's definition. The view must not have any GROUP BY or HAVING clause in it's definition. The view must not have any SUBQUERIES in it's definitions. If the view you want to update is based upon another view, the later should be updatable. Any of the selected output fields (of the view) must not use constants, strings or value expressions.

Sample table : orders This view is a not an updatable view.

Aggregate function 'COUNT' have been used in the definition of the view so this view is not updatable i.e. To execute query on this view In the following topics we are discussing, that a view can not be updated (using a UPDATE VIEW statement) if any of the fields of the view is created by using an arithmetic expression.

In my script, I currently have it limited to pull only the tables that have nchar types, assuming that the views that are fed by the tables will know to update their values. TABLE_NAME) where cols.data_type ='nchar' and tabs. TABLE_TYPE = 'BASE TABLE' open c fetch next from c into @tn, @cn, @ln while @@FETCH_STATUS = 0 begin set @sql = 'alter table ' + @tn + ' alter column ' + @cn + ' nvarchar(' + convert(nvarchar(50), @ln) + ')' exec sp_executesql @sql set @sql = 'update ' + @tn + ' set ' + @cn + ' = LTRIM(RTRIM(' + @cn + '))' exec sp_executesql @sql fetch next from c into @tn, @cn, @ln end close c deallocate c CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW AS SELECT a, b FROM dbo.x; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT a,b INTO #blat FROM dbo.vx; GO EXEC sp_columns N'#blat'; GO DROP VIEW dbo.vx; DROP TABLE dbo.x, #blat; so that I can't alter a table without knowing about the views and other objects it affects - this can make cowboy development/deployment painful, but I guess that's kind of the point).

Is this assumption correct, or should I be including views in this list as well? You can build the script to refresh all referenced views dynamically like this (this is for a single table; you'll need to incorporate this into your existing script to make it dynamic for all affected tables): DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'EXEC sp_refreshview ''' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';' FROM sys.sql_expression_dependencies AS d INNER JOIN sys.views AS v ON d.referencing_id = v.[object_id] INNER JOIN sys.schemas AS s ON v.[schema_id] = s.[schema_id] WHERE d.referenced_id = OBJECT_ID('dbo.whatever') GROUP BY s.name, v.name; EXEC sp_executesql @sql; CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW AS SELECT * FROM dbo.x; GO SELECT * INTO #b1 FROM dbo.vx; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT * INTO #b2 FROM dbo.vx; GO ALTER TABLE dbo.x ADD d INT; GO SELECT * INTO #b3 FROM dbo.vx; GO EXEC sp_rename N'dbo.x.b', N'c', N'COLUMN'; EXEC sp_rename N'dbo.x.d', N'b', N'COLUMN'; GO SELECT * INTO #b4 FROM dbo.vx; GO EXEC sp_columns N'#b1'; EXEC sp_columns N'#b2'; EXEC sp_columns N'#b3'; EXEC sp_columns N'#b4'; GO DROP TABLE #b1, #b2, #b3, #b4; GO -- initial: #b1____ a int 10 #b1____ b nchar 4 -- correct: #b2____ a tinyint 3 #b2____ b nvarchar 4 -- missing new column d: #b3____ a tinyint 3 #b3____ b nvarchar 4 -- missing column c, b still points at "old" b: #b4____ a tinyint 3 #b4____ b nvarchar 4 Great answer in several ways: made it understandable, gave an executable example, and provided two other tips I hadn't heard of before. I'll be putting all of those (including testing with test tables, etc.) to use in the future.

For reference, the script in its current form is below (mostly taken from this great SO answer): declare @tn nvarchar(128) declare @cn nvarchar(128) declare @ln int declare @sql as nvarchar(1000) declare c cursor for select cols.table_name,cols.column_name,cols.character_maximum_length from information_schema.columns cols inner join information_schema.tables tabs on (cols. The SQL UPDATE VIEW command can be used to modify the data of a view. Here in the follwing topics we are discussing, that a view can not be updated (using a UPDATE VIEW statement) if any of the fields of the view is created by using either an AGGREGATE FUNCTION or a GROUP BY clause.If you have the Northwind database you can see that it has several views installed by default.The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table.Someone mistakenly created a bunch of nchar fields in database tables.I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside.

Comments are closed.