Insert Into table from another

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

General SQL Server Forums

New to SQL Server Programming

Insert Into table from another

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-10-31 : 12:05:06

I need to insert data from one table into another table that have different column names and I only need to insert certain fields.
Should I use Insert into? And how do I reference the from table when the column names are different?

Insert into Clients (Clientname, address, phone)
from contacts (contactname, contactaddress, contactphone)
where contactname = 'DOS'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 12:07:00

[code]Insert into Clients (Clientname, address, phone)
select contactname, contactaddress, contactphone
from contacts
where contactname = 'DOS'[/code]

KH
[spoiler]Time is always against us[/spoiler]

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-10-31 : 12:13:11

What do I do for fields in my insert table that are not in my from table? Do I list these in the insert as null and ignore them in the select?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 12:15:47

You should be asking the question to yourself. What value do you want to insert it with ?

KH
[spoiler]Time is always against us[/spoiler]

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-10-31 : 12:22:32

Sorry, I'm having trouble understanding how the insert knows which fields to grab from the select and where to put them in the table. if I set nulls in the insert then the select statement will skip this insert and select it's next field?

Insert into clients (clientname, address, null, phone)
select contactname, contactaddress, contactphone
from contacts
where contacts = 'dos'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 12:27:48

assuming your clients table has 4 columns and if you only specify 3 columns in the insert statement, the column not specified will be inserted with NULL value.


Insert into clients (clientname, address, phone)
select contactname, contactaddress, contactphone
from contacts
where contacts = 'dos'

If you want to insert a constant value into the column, you can do this


Insert into clients (clientname, address, phone, fax)
select contactname, contactaddress, contactphone, 'NO FAX'
from contacts
where contacts = 'dos'

KH
[spoiler]Time is always against us[/spoiler]

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-10-31 : 12:35:42

Thank you so much KH! You're information has been extremely helpful!

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-01 : 06:25:27

try this

insert into target_table
select [column_name] from [source_table] where [apply conditio]

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 07:13:25

Sorry Rahul, but I'm not understanding how that is different to khtan's suggestion. Can you explain please?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 07:15:05

quote:
Originally posted by Kristen

Sorry Rahul, but I'm not understanding how that is different to khtan's suggestion. Can you explain please?

Kristen

He starts posting without reading any replies. Read all his recent posts

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 07:45:37

What a waste of time then ...

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-01 : 13:13:12