Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create dynmaic virtual table using meata data tables Expand / Collapse
Author
Message
Posted Monday, May 27, 2013 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 6:50 AM
Points: 1, Visits: 10
Hi

i want to build query at runtime.

i have some metadata tables where i can store table names and attribute names. so now how can i do CRUD operations.
Post #1456985
Posted Monday, May 27, 2013 1:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:41 AM
Points: 486, Visits: 422
Create a SQL in a Variable and execute using sp_executesql procedure.
Post #1456988
Posted Monday, May 27, 2013 1:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:06 AM
Points: 1,608, Visits: 374
DECLARE @GblTableName NVARCHAR(300)
SET @GblTableName = '[tempdb].[dbo].[Temp' + REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', '') + ']'

EXEC ('IF OBJECT_ID(''' + @GblTableName + ''') IS NOT NULL
DROP TABLE ' + @GblTableName + '')


exec('CREATE TABLE ' + @GblTableName + '
(
UserID NVARCHAR(100) ,
ProductID NVARCHAR(100) ,
Price NVARCHAR(100) ,
ProductQty NVARCHAR(100) ,
TotalPrice NVARCHAR(100) ,
ReportPrice NVARCHAR(100)
)')

SET @ExecuteSql = N'INSERT INTO ' + @GblTableName + ' SELECT UserID ,ProductID ,Price,ProductQty,TotalPrice,ReportPrice
FROM ( SELECT USERID ,
PRODUCTID ,
PRICE ,
PRODUCTQTY ,
TOTALPRICE ,
REPORTPRICE ,
DATAORDER
FROM ABC) T '

EXEC (@ExecuteSql)
Post #1456990
Posted Monday, May 27, 2013 1:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:06 AM
Points: 1,608, Visits: 374
Above example is only for reference and not the perfect solution.
Post #1456992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse