Posted by Erland Sommarskog on 03/10/06 00:41

> Now, what I have in mind is to create a table such that all Database is
> stored in it. Since
> the application will load only those fields into the labels which are
> mapped, it wouldnt create a problem.
> Now if I have 100 records in table 1 and 10 records in table 2, the
> table 3 should have 110 records at the end of operation. This table
> alone will be accessed by the application to do its work.
> My question is how to do this? I mean how can I take the records from
> table 1, put them in table 3, then take the records from table 2 and
> put them in table 3 , and so on?

To make a completely wild guess, this may be what you are looking for:

INSERT tbl3(PART_NUM, PART_NAME, DESC1, DESC2)
SELECT PART_NUM, PART_NAME, DESC1, DESC2
FROM tbl1

INSERT tbl3(PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2)
SELECT PART_NUM, CAT_NUM, CAT_NAME, SDESC1, SDESC2
FROM tbl2 a
WHERE NOT EXISTS (SELECT *
FROM tbl3 WHERE a.PART_NUM = b.PART_NUM)

UPDATE tbl3
SET CAT_NUM = b.CAT_NUN,
CAT_NAME = b.CAT_NAME,
SDESC1 = b.SDESC1,
SDESC2 = b.SDESC2
FROM tbl3 a
JOIN tbl2 b ON a.PART_NUM = b.PART_NUM

>One more question, if I have a table in SQL,
>can I alter a field to make it primary key?
>Or do I have to to it while creating the table itself?

You cannot alter the field to make it a PK, but you can alter the table
to define a PK, if it does not have one. And PK can have more than one
column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]