Copying rows from multiple tables to a single table

Hi,

I have 3 tables with the follwing schema

Table <Category>

{

UniqueID,

LastDate DateTime

}

Assume the follwing tables with data following the above schema

Table Cat1

{

1, D1

2, D2

3, D3

}

Table Cat2

{

2, D4

3,D5

4, D6

}

Table Cat3

{

1, D7

3,D8

5,D9

}

I have a Master and the schema is as follows

Table master

{

UniqueId,

Cat1 DateTime, -- This is same as the Table name

Cat2 DateTime, -- This is same as the Table name

Cat3 DateTime -- This is same as the Table name

}

After inserting the data from all these 3 tables, I want the my master table to look like this

Table Master

{

UniqueId cat1 cat2 Cat3

- --

1 D1 NULL D7

2 D2 D4 NULL

3 D3 D5 D8

4 NULL D6 NULL

5 NULL NULL D9

}

Please remember the column names will be same as that of table names

can any one pelase let me know the query t o acheive this

Thanks for your quick response

~Mohan Babu

Try:

Code Snippet

insert into table_master([uniqueid], cat1, cat2, cat3)

select

[id],

[Cat1],

[Cat2],

[Cat3]

from

(

select [id], 'Cat1' as table_name, lastdate from dbo.Cat1

union all

select [id], 'Cat2' as table_name, lastdate from dbo.Cat2

union all

select [id], 'Cat3' as table_name, lastdate from dbo.Cat3

) as a

pivot

(

max(lastdate)

for table_name in ([Cat1], [Cat2], [Cat3])

) as pvt

go

AMB

|||

Can you use full outter join style?

CREATE Table #Cat1

(

id int,

val varchar(10)

)

insert into #cat1 (id, val) values (1, 'D1')

insert into #cat1 (id, val) values (2, 'D2')

insert into #cat1 (id, val) values (3, 'D3')

CREATE Table #Cat2

(

id int,

val varchar(10)

)

insert into #cat2 (id, val) values (2, 'D4')

insert into #cat2 (id, val) values (3, 'D5')

insert into #cat2 (id, val) values (4, 'D6')

SELECT

CASE

WHEN C1.Id IS NOT NULL THEN C1.Id

WHEN C2.Id IS NOT NULL THEN C2.Id

ELSE NULL

END AS [Id],

C1.val,

C2.val

FROM #Cat1 c1

FULL OUTER JOIN #cat2 C2 ON

(C1.Id = C2.Id)

DROP TABLE #Cat1

DROP TABLE #Cat2

|||

Code Snippet

Create Table #cat1 (

id int,

[LastDate] datetime

);

Insert Into #cat1 Values('1','01/01/07');

Insert Into #cat1 Values('2','01/02/07');

Insert Into #cat1 Values('3','01/03/07');

Create Table #cat2 (

id int,

[LastDate] datetime

);

Insert Into #cat2 Values('2','01/02/07');

Insert Into #cat2 Values('3','01/03/07');

Insert Into #cat2 Values('4','01/04/07');

SELECT c.id, c.LastDate, d.LastDate FROM (SELECT a.id, b.LastDate FROM (SELECT id FROM #cat1

union

SELECT id FROM #cat2) a LEFT JOIN #cat1 b ON a.id=b.id) c LEFT JOIN #cat2 d ON c.id=d.id

|||

Hi AMB and limon

Thank you bothe very much for your queries.

Both works perfect.

AMB: Can you pelase explain me why u have used Pivt and what is the impact of it in terms of performace and also can u please explain me why you have used max(lastUpdate) and what is the use of it

Limon : Though it works fine i feel the query is bit long.

My concern is that i may have N number of category tables and i have to merge them into a single table.

So i need to build the a query dynamically based on the number of the tables and merge them.

Can any one please explaine me which one executes faster as i might have millions of rows in the category table

Thanks for your time

~Mohan

|||

Dave,

This query inserts null if the ID doesn't exist in botht the tables.

Thanks for your time

~Mohan

|||

Hi mohandbabud,

why did I use pivot?

Because that is what you are doing. You are pivoting rows to columns. When using pivot operator, you need an aggregation function, and It does not make sense to use SUM, AVG, etc, with a [datetime] data type. you could use MIN also. BTW, I supposed that the [id] is unique in each category table.

About which one one will execute faster, I do not know, because both queries are doing heavy stuff. You can tell us when you have finished.

AMB