How to join columns from 3 differrent tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to join columns from 3 differrent tables

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-24 : 07:10:08

Hi...Iam trying to join all the colums from different tables into single table in one databse
in my script i am using two databses and individual tables,Those table name RECORD_COUNT,INSERT_COUNT AND UPDATE_COUNT
In my final table TOTAL_RECORD_COUNT i am looking for all the columns Table_Name,Row_count,Insert_Count and Update_count

could you pls help mee in joining these columns from the below script.

DECLARE @TableName sysname,
@SQL nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
DECLARE @LOOP_COUNT int
SET @LOOP_COUNT = 1

WHILE (@LOOP_COUNT < 3)
BEGIN

IF @LOOP_COUNT = 1 USE PAS_SCHEMA
IF @LOOP_COUNT > 1 USE PAS_RDB

DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROWS_COUNT ' +

'FROM ['+ @TableName+']'

print @sql

EXEC SP_EXECUTESQL @SQL

SELECT @SQL =
'insert into INSERT_COUNT (TABLE_NAME, INSERT_COUNT)
SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as INSERT_COUNT ' +

'FROM [' + @TableName + '] where' + convert(varchar, @INSERT_DATE) +' >= '''+ convert(varchar, @TODAY) + ''' group by ' + @tablename + ''

print @sql

EXEC SP_EXECUTESQL @SQL

SELECT @SQL =
'insert into UPDATE_COUNT (TABLE_NAME, UPDATE_COUNT)
SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as UPDATE_COUNT' +

'FROM [' + @TableName + '] where' + convert(varchar, @UPDATE_DATE) +' >= '''+ convert(varchar, @TODAY) + ''' group by ' + @tablename + ''

print @sql
EXEC SP_EXECUTESQL @SQL

FETCH NEXT FROM tables_cursor INTO @TableName

END

CLOSE tables_cursor

DEALLOCATE tables_cursor

SET @LOOP_COUNT = @LOOP_COUNT + 1

END

thanx

Satya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-24 : 14:33:06

Join wouldn't be used in this scenario as joins are used to link tables together that have related columns, such as a PK/FK relationship. You can UNION them together instead, but I'm unclear as to what you are doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources