Appending tables

#11

I am expecting 110 rows.

All i want is that first all records from table 1 are added to table 3.
Then all records from table 2 are added to table 3.
The fields which are not present in table 1 or table 2 are left blank.

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?

#12

Quote:

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

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#13

Erland Sommarskog wrote:

Quote:

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

Thanks, but I found out another way to get the job done.

Quote:

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.

Agreed, I can use
ALTER TABLE <tablename> ADD PRIMARY KEY (<fieldname>);
to alter the table and define a PK. But it works only if the
<fieldname> is
NOT NULL. Is there any way I can alter the table to make the field NOT
NULL?

Quote:

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#14

Shwetabh (shwetabhgoel (AT) gmail (DOT) com) writes:

Quote:

Is there any way I can alter the table to make the field NOT
NULL?

ALTER TABLE ALTER COLUMN

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Appending tables

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Appending tables - 03-08-2006 , 10:17 PM

Re: Appending tables - 03-09-2006 , 04:41 PM

Re: Appending tables - 03-10-2006 , 12:30 AM

Re: Appending tables - 03-10-2006 , 02:33 AM