August 22, 2012 at 1:58 pm
Hi ,
Can i write the following query like this,
INSERT INTO #gearsdata (ACC_ID,
ACC_YEAR,
ACC_DATE,
ACC_ATIME,
COUNTY_DESC,
ROUTE_DESC,
INTRSCT_RT_TYPE,
INTRSCT_RT,
ACC_TNI,
ACC_TNF,
ACC_TNV,
HARMFULEVENT_DESC,
COLLISION_DESC,
LOCIMPACT_DESC,
LIGHT_DESC,
SURFACE_DESC,
RAMPSECTION_ID,
D1,
D2,
D3,
VM1,
VM2,
VM3,
PDF_LINK,
ORIGINALROUTE,
RCLINK)
select * from OPENQUERY(EDWGEARS,'SELECT IncidentIdNbr , incident_year, incident_date, incident_time ,county_desc, routetype_desc,intersection_rttype_desc,
intersection_route, InjuriesNbr_Total, FatalitiesNbr_Total, totalNbrOfVehicle, firstharmfulevent_desc, mannerofcollision_desc,
locationatimpact_desc, lightcondition_desc,surfacecondition_desc, RampSection, directionnmovement1_desc, directionnmovement2_desc,
directionnmovement3_desc,vehiclemaneuver1_desc, vehiclemaneuver2_desc,
vehiclemaneuver3_desc, PDF_LINK, null as ORIGINALROUTE, RCLINK
FROM VW_GEARS_REPORT_DATA WHERE RCLINK IS NOT NULL and LatDecimal IS NOT NULL and LongDecimal IS NOT NULL') a
INNER JOIN #RptParms b on a.IncidentIdNbr = b.Incidents
and a.incident_year = b.Years
August 22, 2012 at 2:07 pm
That should be fine, are you running into an error of some kind?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 2:10 pm
Yes, I am getting the following error:
(2533 row(s) affected)
Msg 121, Level 15, State 1, Procedure GEARSAnalysisReportsGIS2, Line 545
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
Thanks
Aj
August 22, 2012 at 2:32 pm
That's most likely because of your select * from statement.
State the exact columns you want from both the local #tmp and the Openquery aliases to feed into the insert. My guess is you're grabbing columns with it from the #tmp you don't want, but they will appear with a *.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 22, 2012 at 3:46 pm
Thanks a lot for all your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply