.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Sql Multiple table data retrival

Posted By: Aamrapali Tamgadge     Posted Date: April 28, 2014    Points:5   Category :Sql Server
hello i m having about 200 tables it may be variable i want to retrive the column values from that 200 tables coulmn name is not fixed and the column may b different in diff. tables but want to put it in a single column by retriving from 200 multiple tables.
how can i do this.

Responses
Author: abhays             
Posted Date: April 28, 2014     Points: 20   

Please explain your requirement with four or five sample tables.

Author: Aamrapali Tamgadge             
Posted Date: April 29, 2014     Points: 20   

hi . m having one master table in that all the tablename and columnnames which i want to retrive are stored ..
i have to retrive the data from all these tables and get result in single table

Software Programmer At OTIS ISRC
Author: Aamrapali Tamgadge             
Posted Date: April 29, 2014     Points: 20   

hi . m having one master table in that all the tablename and columnnames which i want to retrive are stored ..
i have to retrive the data from all these tables and get result in single table

Software Programmer At OTIS ISRC
Author: Aamrapali Tamgadge             
Posted Date: April 29, 2014     Points: 20   

hi i made the below Sp. but the problem here is that it is retriving only the last table data. i want al the table data .

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE usp_LTQuery
AS
BEGIN
declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM LangTransTableDetail)
--declare @TotalRecords int = 10
Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''

WHILE @RowCount <= @TotalRecords
BEGIN

SELECT @TableName = TableName, @ColumnName = TableCaption
FROM LangTransTableDetail



SET @sql = 'Select A.ET as ''English Values'', B.FT as ''Translated Values''
From (SELECT distinct '+@ColumnName+' AS ET
FROM '+@TableName+ ' WHERE LanguageId=0 AND DeleteFlag=0) A
,
(SELECT distinct '+@ColumnName+' AS FT
FROM '+@TableName+' WHERE LanguageId = 6 AND DeleteFlag=0) B
WHERE
ET IS NOT NULL AND ISNumeric(ET) <> 1 AND
( LTRIM(RTRIM(ET)) <> '''' AND LTRIM(RTRIM(ET)) <> '''')'



IF @RowCount != @TotalRecords
BEGIN
set @sql = @sql + 'UNION ALL'
END

SET @RowCount = @RowCount + 1
END

print @sql
exec(@sql)
END
GO


exec usp_LTQuery

Software Programmer At OTIS ISRC
Author: Aamrapali Tamgadge             
Posted Date: April 29, 2014     Points: 20   

hi i replace my Sp as follows i m getting the result from all the table but in english values i m getting the name of column .. i want its values.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE usp_LTQuery
AS
BEGIN
declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM LangTransTableDetail)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''
SET @sql = ''
WHILE @RowCount <= @TotalRecords
BEGIN
begin
SELECT @TableName = TableName, @ColumnName = TableCaption
FROM LangTransTableDetail
end

begin
SELECT @sql = @sql + ' Select A.ET as ''EnglishValues'', B.FT as ''TranslatedValues''
From (SELECT distinct '+@ColumnName+' AS ET
FROM '+@TableName+ ' WHERE LanguageId=0 AND DeleteFlag=0) A
,
(SELECT distinct '+@ColumnName+' AS FT
FROM '+@TableName+' WHERE LanguageId = 6 AND DeleteFlag=0) B
WHERE
ET IS NOT NULL AND ISNumeric(ET) <> 1 AND
( LTRIM(RTRIM(ET)) <> '''' AND LTRIM(RTRIM(ET)) <> '''')'



IF @RowCount != @TotalRecords
BEGIN
set @sql = @sql + 'UNION ALL'
--create table #temp(EnglishValues nvarchar(max),Translated nvarchar(max))
--insert into #temp values(@sql)
END

SET @RowCount = @RowCount + 1
END
end


print @sql
exec(@sql)
END
GO


exec usp_LTQuery





Software Programmer At OTIS ISRC
Author: Aamrapali Tamgadge             
Posted Date: April 30, 2014     Points: 20   

hi i have replace my query as follws but only taking the record of last tables till the iteration.
i want all table data.







-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE usp_LTQuery
AS
BEGIN
declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM LangTransTableDetail)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''
SET @sql = ''
WHILE @RowCount <= @TotalRecords
BEGIN
begin
SELECT @TableName = TableName, @ColumnName = TableCaption
FROM LangTransTableDetail
end
print @TableName
begin
--SELECT @sql = @sql +
--' Select A.ET as ''EnglishValues'', B.FT as ''TranslatedValues'' From
-- (SELECT distinct '+@ColumnName+' AS ET FROM '+@TableName+ ' WHERE LanguageId=0 AND DeleteFlag=0) A
-- ,
-- (SELECT distinct '+@ColumnName+' AS FT FROM '+@TableName+' WHERE LanguageId = 6 AND DeleteFlag=0) B
-- WHERE ET IS NOT NULL AND ISNumeric(ET) <> 1 AND( LTRIM(RTRIM(ET)) <> '''' AND LTRIM(RTRIM(ET)) <> '''')'


SELECT @sql = @sql +
'select G1.'+@ColumnName+' as EnglishLanguage
, G2.'+@ColumnName+' as TranslatedLanguage
from (
select row_number() over (order by LanguageId) as rn
, *
from '+@TableName+'
where LanguageId = 0 AND DeleteFlag=0
) as G1
full outer join
(
select row_number() over (order by LanguageId) as rn
, *
from '+@TableName+'
where LanguageId = 6 AND DeleteFlag=0
) as G2
on G1.rn = G2.rn
'
print @sql
IF @RowCount != @TotalRecords
BEGIN
set @sql = @sql + ' UNION ALL '
--create table #temp(EnglishValues nvarchar(max),Translated nvarchar(max))
--insert into #temp values(@sql)
END

SET @RowCount = @RowCount + 1
END
END


print @sql
exec(@sql)
END
GO


exec usp_LTQuery



--select * from Category where LanguageId =6

--select * from LangTransTableDetail

--SELECT distinct name AS ET
-- FROM Category WHERE LanguageId=0 AND DeleteFlag=0


--SELECT distinct name AS FT
-- FROM Category WHERE LanguageId = 6 AND DeleteFlag=0

Software Programmer At OTIS ISRC
Author: Akhil Raj       [Moderator]      
Posted Date: May 19, 2014     Points: 20   

hi,
You can use CTE to iterate each tables. Then inside you can use cursor or similar to get each table data and combine. Contruct dynamic sp so it is eay to to get all the table data at once. In the dynamic sp create union all query to get all table data



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend