Geek asks and answers

Insert fields from one table into another in SQL Server 2005

I have a table from which I need to insert some of its data into another table.

I have tables "Provider" and "1_MAIN - Contacts" (I know a really bad name, let's just call it "Home" in this discussion). 2 tables are connected by the column "Contact_ID". All entries in the Provider table have an entry in the main table with the corresponding "Contact_ID".

In the table "Providers" there are 4 columns called "geri", "adol", "adult" and "pedi". These fields are the bitdefault data type 0.

I created 4 new columns in "Main" with the same name and I want to pull the values ​​from the "Provider" table to the "Main" table.

So basically I want to do the following:

If there is an entry in the "Provider" in the "Main" record, then take the values ​​of the "geri", "adol", "adult" and "pedi" columns from your "Provider" record and copy them into your "Main" record.

+3
source share
2 answers

You need UPDATE, not INSERT:

UPDATE m
SET geri  = p.geri
  , adol  = p.adol
  , adult = p.adult
  , pedi  = p.pedi
FROM Main m
  JOIN Provider p
    ON p.Contact_ID = m.Contact_ID
+3
source

You are looking for "INSERT INTO ... SELECT". Something like (you will need to change this)

Insert Into Main(geri, adol, adult, pedi)
Select p.geri, p.adol, p.adult, p.pedi
 from Provider p
 where p.ProviderID = Main.ProviderID

This is rude because I did not do it after a while, but it should get you pretty close.

+2

More articles:


All Articles