Hi,

I had 3 tables

Tets_06
Test_05
Test_04

I want to combine these 3 tables into one new table 'Test_combined'

--I cant Use UNION ,bcoz the structure of the 3 tables is not same (Test_06
had two extra columns than test_04)

here is the sample DDL:

CREATE TABLE TEST_06
(
ID      INT IDENTITY PRIMARY KEY,
NAME    VARCHAR(15),
SALARY  INT ,
DESIG   VARCHAR(15)
)


CREATE TABLE TEST_05
(
ID      INT IDENTITY PRIMARY KEY,
NAME    VARCHAR(15),
SALARY  INT
)

CREATE TABLE TEST_04
(
ID      INT IDENTITY PRIMARY KEY,
NAME    VARCHAR(15)
)



INSERT INTO TEST_06(NAME,SALARY,DESIG) VALUES('HICHCOCK',20000,'SUSPENCE')
INSERT INTO TEST_06(NAME,SALARY,DESIG) VALUES('SPIELBURG',10000,'FICTION')
INSERT INTO TEST_06(NAME,SALARY,DESIG) VALUES('JOHN WOO',20000,'ACTION')
INSERT INTO TEST_06(NAME,SALARY,DESIG) VALUES('JAMES CAMERON',30000,'LOVE')

INSERT INTO TEST_05(NAME,SALARY) VALUES('KATE',20000)
INSERT INTO TEST_05(NAME,SALARY) VALUES('CATHERINE',10000)
INSERT INTO TEST_05(NAME,SALARY) VALUES('JULIA',20000)
INSERT INTO TEST_05(NAME,SALARY) VALUES('SALMA',30000)


INSERT INTO TEST_04 (NAME) VALUES ('RICH')
INSERT INTO TEST_04 (NAME) VALUES ('SANTVEER')
INSERT INTO TEST_04 (NAME) VALUES ('ADAM')

create table cycle
(
  cycle_id    int primary key,
  Table_name  varchar(15),
  status      varchar(15)
)

INSERT INTO CYCLE (CYCLE_ID,TABLE_NAME,STATUS) VALUES
(001,'TEST_06','PUBLISHED')
INSERT INTO CYCLE (CYCLE_ID,TABLE_NAME,STATUS) VALUES
(002,'TEST_05','PUBLISHED')
INSERT INTO CYCLE (CYCLE_ID,TABLE_NAME,STATUS) VALUES
(003,'TEST_04','PUBLISHED')


I have to take structure of "Test_06" for my new table 'Test_combined' and
one extra column 'cycle_id' from "cycle" table ....
ID and CYCLE_ID will become the composite primary for my new 'Test_combined'
table

Is there any way to do with UNION or is there any other approach

Thanks
Kumar

Kumar wrote:

No need to repost. I have replied in your thread from yesterday.

Yes you can use UNION. Just add NULLs or some sensible default value in
the SELECT list of the UNION. Like:

SELECT id, name, salary, desig
FROM test_06
UNION ALL
SELECT id, name, 0, ''
FROM test_04 ;



That DDL is all seriously bad news. Apparently you have redundant data
everywhere and no business keys (IDENTITY PK does not prevent
duplicates). You should clean up and add some UNIQUE constraints before
you try to merge. Of course it maybe that the DDL was just simplified
and that you left out the alternate keys...

the SELECT list of the UNION. Like:

SELECT id, name, salary, desig
FROM test_06
UNION ALL
SELECT id, name, 0, ''
FROM test_04 ;

--------------
David,

I just simplified the table   ...Actullally i had like 30-35 columns in
every table(Around 30 tables ) so when iam merging 30 tables in to one
....each time i cant compare each and every column with current table

Iam thinking about one logic ,by using the follwing query i can get the
missed colmns list in "test_04" by comparing TEST_06..Is there any way to
make logic like "if any colmn missed in the comparision with the main
table(test_06),make the column values of that particular table (test_04) null
"

Select    COLUMN_NAME
From    INFORMATION_SCHEMA.Columns
Where    TABLE_NAME = 'test_06'
AND    COLUMN_NAME NOT IN (
        SELECT     COLUMN_NAME
        FROM     INFORMATION_SCHEMA.Columns
        Where    TABLE_NAME = 'test_04')

I dont know whether iam thinking right or not? Pls provide some solutions

Kumar wrote:
Why not? That's quite a small and straightforward set of tables by most
people's standards. Use Query Analyzer. You can pull the column lists
from the Object Browser and edit this query with just a few minutes
work.

Certainly you can use the information schema to help you but to write a
query that generates the UNION for you, then to check and test that
query is probably going to be no less work than writing the UNION
yourself.

Newbie: Timeout On IN statement
Getting an unused number from a set of numbers?
Design: multiple columns for primary key
Whats Wrong with the Query?
TSQL Debugger Oddity