updatable views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updatable views

Author

Topic

BCullenward
Starting Member

28 Posts

Posted - 2009-06-24 : 09:45:13

Is there a simple way to update multiple tables through a view? Due to performance issues on the server, I have several databases that all have the same layout and same tables but different data in each table. Most of the stored procs involve unioning the tables from each database together:


LEFT JOIN
(
   SELECT 1 AS lArea, lProductID, lQuantity
   FROM Products.dbo.Products
   UNION
   SELECT 2 AS lArea, lProductID, lQuantity
   FROM Products02.dbo.Products
   UNION
   SELECT 3 AS lArea, lProductID, lQuantity
   FROM Products03.dbo.Products
)

On occasion new databases are added causing all the stored procedures to need to be updated. This process takes a few hours and is difficult to maintain.

I was planning on creating a view or function and then joining on that, the problem however is some of the stored procs update the tables in much the same way:


   UPDATE p
   SET p.lQuantity = (p.lQuantity - qs.lAmountSold)
   FROM Products.dbo.Products p
   INNER JOIN #tblQtySold qs
     ON p.lProductID = qs.lProductID
   WHERE qs.lArea = 1

   UPDATE p
   SET p.lQuantity = (p.lQuantity - qs.lAmountSold)
   FROM Products02.dbo.Products p
   INNER JOIN #tblQtySold qs
     ON p.lProductID = qs.lProductID
   WHERE qs.lArea = 2

   UPDATE p
   SET p.lQuantity = (p.lQuantity - qs.lAmountSold)
   FROM Products03.dbo.Products p
   INNER JOIN #tblQtySold qs
     ON p.lProductID = qs.lProductID
   WHERE qs.lArea = 3

Again, this is less than ideal. What would be the best way to handle this situation (changing the infrastructure is not an option)?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:52:07

If you are using SQL Server 2005 or later, you should consider using SYNONYMs.

E 12°55'05.63"
N 56°04'39.26"

BCullenward
Starting Member

28 Posts

Posted - 2009-06-24 : 10:05:26

quote:
Originally posted by Peso

If you are using SQL Server 2005 or later, you should consider using SYNONYMs.

E 12°55'05.63"
N 56°04'39.26"

We are using SQL Server 2005, but how are synonms going to solve the problem? It looks like they are essentially aliases that can be used outside that particular SQL statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 10:12:17

Because you can loop the update statements and drop/recreate the synomym for each iteration.

Similar to this:

DECLARE	@i INT

SET	@i = 1

WHILE @i <= 3
	BEGIN
		IF @i = 1
			EXEC('CREATE SYNONYM MyTable FOR Products.dbo.Products')
		ELSE
			EXEC('CREATE SYNONYM MyTable FOR Products' + RIGHT('0' + CAST(@i AS VARCHAR(2)), 2) + '.dbo.Products')

		UPDATE	p
		SET		p.lQuantity = (p.lQuantity - qs.lAmountSold)
		FROM		MyTable AS p
		INNER JOIN	#tblQtySold AS qs ON p.lProductID = qs.lProductID
		WHERE		qs.lArea = @i

		DROP SYNONYM	MyTable

		SET	@i = @i + 1
	END

E 12°55'05.63"
N 56°04'39.26"

BCullenward
Starting Member

28 Posts

Posted - 2009-06-24 : 12:41:19

quote:

Originally posted by Peso

Because you can loop the update statements and drop/recreate the synomym for each iteration.

Similar to this:

DECLARE	@i INT

SET	@i = 1

WHILE @i <= 3
	BEGIN
		IF @i = 1
			EXEC('CREATE SYNONYM MyTable FOR Products.dbo.Products')
		ELSE
			EXEC('CREATE SYNONYM MyTable FOR Products' + RIGHT('0' + CAST(@i AS VARCHAR(2)), 2) + '.dbo.Products')

		UPDATE	p
		SET		p.lQuantity = (p.lQuantity - qs.lAmountSold)
		FROM		MyTable AS p
		INNER JOIN	#tblQtySold AS qs ON p.lProductID = qs.lProductID
		WHERE		qs.lArea = @i

		DROP SYNONYM	MyTable

		SET	@i = @i + 1
	END

E 12°55'05.63"
N 56°04'39.26"

That makes sense. There is an issue with your EXEC and the RIGHT though. That needs to be put in a variable first, and then can be executed.

So it'd be like this:


  DECLARE @SQL VARCHAR(100)

  IF @i = 1
	SET @SQL = 'CREATE SYNONYM MyTable FOR Products.dbo.Products'
  ELSE
	SET @SQL = 'CREATE SYNONYM MyTable FOR Products' + RIGHT('0' + CAST(@i AS VARCHAR(2)), 2) + '.dbo.Products'

  EXEC(@SQL)

otherwise it looks like it should work.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 14:59:39

Hence the "similar to this"

If you don't want SYNONYMs, you can make the complete update statement as dynamic sql in same fashion.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources