Appending table with only partially same columns

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)

Appending table with only partially same columns

Author

Topic

fossie
Starting Member

4 Posts

Posted - 2008-11-03 : 09:37:37

Hi, fellows

I have two tables, one main table and one ad hoc table. Column-wise the ad hoc table contains a subset of the main table. The ad hoc table has a set of rows I wish to append to the main table. Columns which do not exist in the ad hoc table should be set to NULL in the main table.

Here's the hitch: Except for the primary key, the column names of table main is not known in the 3rd party software from which I will send/execute the append-command. The command will be sent through odbc.

To exemplify:

use tempdb
go

-- Table main would look like...
create table main (pk int, a varchar(5), b float)
go
insert main (pk, a, b) values(1,'a1',0.1)
insert main (pk, a, b) values(2,'a2',0.2)
insert main (pk, a, b) values(3,'a3',0.3)
go

-- Table adhoc would look like...
create table adhoc (pk int, b float)
go
insert adhoc (pk,b) values(4,0.4)
insert adhoc (pk,b) values(5,0.5)
insert adhoc (pk,b) values(6,0.6)
go

-- The wanted table main would look like as if you added...
insert main (pk,b) values(4,0.4)
insert main (pk,b) values(5,0.5)
insert main (pk,b) values(6,0.6)
go

My best attempt so far is

insert into main select *
from adhoc tmp
where not exists
(select * from main tbl where tmp.pk=tbl.pk)

..which complains that the column names need to be exactly the same in the two tables.

Any clues on how to bypass this?

-Fossie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:47:24

since adhoc tables columns are known, will you be only updating those column values of main table? in such cases, isnt below enough?

insert into main (b)
SELECT b FROM adhoc

set other columns in main table to be of NULLABLE type .

fossie
Starting Member

4 Posts

Posted - 2008-11-03 : 10:01:03

Ah, of course.
Thank you for sorting me out.

Cheers,
-Fossie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 10:05:02

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources