Is this possible- INSERT INTO t1(f1,f2) FROM (SELECT f1,f2 FROM t2) ?

[AndyHopper]

Is is possible to insert in a manner similar to that shown below? This query won't run; however it shows approximately what I'd like to accomplish.

INSERT INTO [agentmaster].[dbo].[property]
(property.bath_level, property.bath3_4_level, property.baths, property.baths3_4, property.bd1_size, property.bd2_size, property.bd3_size,
property.bd4_size, property.bedrooms, property.book, property.color, property.den_size, property.dr_size, property.elem_school, property.flood_zone,
property.found_size, property.fr_size, property.frontage, property.glaag, property.glaaga, property.glaagb, property.high_school, property.kt_size,
property.lav_level, property.lavs, property.list_price, property.lister_agent_name, property.lister_office_name, property.lot, property.lot_size_acres,
property.lr_size, property.map, property.midl_school, property.nof_cars, property.ot_size, property.page, property.parking, property.remark1,
property.rooms, property.school_district, property.seasonal, property.status, property.surveyed, property.tax_years, property.taxes, property.town,
property.water_body, property.water_frontage, property.year_built, property.mls_zone, property.mod_date_time, property.p_mod_date_time,
property.features)
FROM (SELECT idxmls1.bath_level, idxmls1.bath3_4_level, idxmls1.baths, idxmls1.baths3_4, idxmls1.bd1_size, idxmls1.bd2_size, idxmls1.bd3_size,
idxmls1.bd4_size, idxmls1.bedrooms, idxmls1.book, idxmls1.color, idxmls1.den_size, idxmls1.dr_size, idxmls1.elem_school,
idxmls1.flood_zone, idxmls1.found_size, idxmls1.fr_size, idxmls1.frontage, idxmls1.glaag, idxmls1.glaaga, idxmls1.glaagb,
idxmls1.high_school, idxmls1.kt_size, idxmls1.lav_level, idxmls1.lavs, idxmls1.list_price, idxmls1.lister_agent_name,
idxmls1.lister_office_name, idxmls1.lot, idxmls1.lot_size_acres, idxmls1.lr_size, idxmls1.map, idxmls1.midl_school, idxmls1.nof_cars,
idxmls1.ot_size, idxmls1.page, idxmls1.parking, idxmls1.remark1, idxmls1.rooms, idxmls1.school_district, idxmls1.seasonal,
idxmls1.status, idxmls1.surveyed, idxmls1.tax_years, idxmls1.taxes, idxmls1.town, idxmls1.water_body, idxmls1.water_frontage,
idxmls1.year_built, idxmls1.mls_zone, idxmls1.mod_date_time, idxmls1.p_mod_date_time, idxmls1.features
FROM [nnren].[mlsnhsql].[dbo]..[idxmls1]
WHERE (id NOT IN
(SELECT property.mls
FROM [agentmaster].[dbo].[property])))

Thanks in advance!
-Andy

 

[tlbroadbent]

The query contains some incorrect and unneeded syntax. I've removed the table names from the column names to simplify and improve readability. I removed unneeded brackets and parentheses. You'll note the correct INSERT INTO syntax

INSERT INTO TABLE1 (<col list>)
SELECT <col list> FROM table2


There is an additional problem with the table name in the SELECT statment. I don't know if that is just a typo when posting the query or an error in the query.

INSERT INTO agentmaster.dbo.property
(bath_level, bath3_4_level, baths,
baths3_4, bd1_size, bd2_size, bd3_size,
bd4_size, bedrooms, book, color, den_size,
dr_size, elem_school, flood_zone,
found_size, fr_size, frontage, glaag,
glaaga, glaagb, high_school, kt_size,
lav_level, lavs, list_price,
lister_agent_name, lister_office_name,
lot, lot_size_acres, lr_size, map,
midl_school, nof_cars, ot_size, page,
parking, remark1, rooms, school_district,
seasonal, status, surveyed, tax_years, taxes,
town, water_body, water_frontage, year_built,
mls_zone, mod_date_time, p_mod_date_time, features)

SELECT
bath_level, bath3_4_level, baths, baths3_4,
bd1_size, bd2_size, bd3_size, bd4_size,
bedrooms, book, color, den_size, dr_size,
elem_school, flood_zone, found_size, fr_size,
frontage, glaag, glaaga, glaagb, high_school,
kt_size, lav_level, lavs, list_price,
lister_agent_name, lister_office_name,
lot, lot_size_acres, lr_size, map, midl_school,
nof_cars, ot_size, page, parking, remark1,
rooms, school_district, seasonal, status,
surveyed, tax_years, taxes, town, water_body,
water_frontage, year_built, mls_zone,
mod_date_time, p_mod_date_time, features

--verify the syntax and name of the table
FROM nnren.mlsnhsql.dbo.idxmls1
WHERE id NOT IN
(SELECT mls FROM agentmaster.dbo.property)

You might want to use the NOT EXISTS clause in place of the NOT IN clause.

WHERE NOT EXISTS
(Select * From agentmaster.dbo.property
Where mls=idxmls1.id) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[AndyHopper]

Thanks Terry!!!

I really appreciate your clear, concise and insightful responses.

Cheers,
Andy