Using Cursor to retrieve multiple table data to return as dataset
-
In a BI project I'm trying to retrieve multiple company information.
I'm not at all familiar with the t-sql world of possibilities..
I have a MasterTable (Object) witch contains all company names and all tablenames.
With this data I'm trying to select each comapny table data into one dataset.
Until now I have formulated an SQL that returns the tablenames for e.g "Items".
This results in 5 rows containing the tablenames
dbo.Company1$[Item]
dbo.Company2$[Item]
And so on..
With this data I need to select certain columns and return them for further use.
What I have until now is this:
if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')
drop procedure dbo.proc_simple
go
create procedure dbo.proc_simple(@_tableName nvarchar(64))
as
begin
-- Fetch the Company and Table Names as Cursor
DECLARE my_cursor CURSOR FOR
SELECT TOP (100) PERCENT [Company Name], Name, 'dbo.' + REPLACE([Company Name], '/', '_') + '$' + '[' + Name + ']' AS Master
FROM dbo.Object
WHERE ([Company Name] <> '') AND ([Name] = @_tableName)
ORDER BY Name, [Company Name]
-- Open the Cursor and Fetch Next
OPEN my_cursor
--NEED TO DO SOMETHING HERE
CLOSE my_cursor
DEALLOCATE my_cursor
--SQL_STATEMENT = some sql where you need TABLE_NAME
--EXECUTE STATEMENT SQL_STATEMENT;
end
go
exec dbo.proc_simple 'Item'
go
-
It is not really clear what you are trying to do or what your question is. Please read the following article on how to get better answers to your questions: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
As Barry said, you were not clear on what you exactly needed. Since, you mentioned you have the table names, then I guess you need to fetch some kind of data from those tables. As the table names are dynamic, so you need a dynamic SQL, something like...
DECLARE @SomeObject VARCHAR(100)SET @SomeObject = 'sys.tables'EXEC( 'SELECT * FROM ' + @SomeObject )--Ramesh
-
Okay, my interpretation
If you need the columns of a table:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName'BTW:
You should avoid SQL keywords as table or column names (object, master).
Greets
Flo
-
I have since used my Google IQ
to formulate this code.It does the job - now I just need to refine the SQL parsing.
Thanks all!
if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P')
drop procedure dbo.proc_simple
go
create procedure dbo.proc_simple(@_tableName nvarchar(64), @genericSQL nvarchar(1000))
as
begin
declare @CompanyName nchar(200)
declare @TableName nchar(200)
declare @sql varchar(1000)
declare @RowNum int
-- Fetch the Company and Table Names as Cursor
DECLARE my_cursor CURSOR FOR
SELECT REPLACE([Company Name], '/', '_') AS CompanyName
FROM dbo.Object
WHERE ([Company Name] <> '') AND ([Name] = @_tableName)
ORDER BY Name, [Company Name]
-- Open the Cursor and Fetch Next
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @CompanyName
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + '[' + rTrim(@CompanyName) + '$' + rTrim(@_tableName) + ']'
--SELECT @sql = 'SELECT * FROM ' + '[' + rTrim(@CompanyName) + '$' + rTrim(@_tableName) + ']'
SELECT @sql =
'SELECT ' +
char(39) + rTrim(@CompanyName) + char(39) + ' AS [CompanyName],' +
Replace(@genericSQL, '*', '[' + rTrim(@CompanyName) + '$' + rTrim(@_tableName) + ']')
EXEC ( @sql)
FETCH NEXT FROM my_cursor INTO @CompanyName
END
CLOSE my_cursor
DEALLOCATE my_cursor
end
go
exec dbo.proc_simple 'Item Translation', '*.[Item No_] ,*.[Description] FROM *'
go
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply