September 3, 2008 at 7:40 am
I am trying to develop a T-SQL statement that will read some records from the database, then insert those records back into the database as new data with the report id changed from 7 to 32?
i've got the following query which extracts the data from the db.
How do I take the results of the query and insert into the multiple tables?
select contact.contactid, hospital.hospitalid,hgr.reportid
from hospitalcontactreports HCR
inner join contact on contact.contactid = hcr.contactid
inner join hospital on hospital.hospitalid = hcr.HospitalID
Inner join hospitalgroupreports hgr on hgr.groupreportsid = hcr.groupreportsid
inner join ReportGlobal RG on rg.reportid = hgr.reportid
Where reportid = 7
Thanks
September 3, 2008 at 8:33 am
Difficult to answer your question with the information you have initially supplied.
Suggest reading the article whose link is in my signature block. That said have you thought of placing your query results into a local temporary table, then using that for your source to insert selected columns into selected additional tables.
September 3, 2008 at 10:34 am
This answers the simple part of your question... You can use this to insert data to differnt tables if you'd like... Just change #YourDesiredTable# Also please read some of the commented lines as there is a small issue with your query.
INSERT INTO
#YourDesiredTable#
(ContactID
,HospitalID
,ReportID
)
SELECT
contact.contactid
,hospital.hospitalid
--,hgr.reportid
-- Instead of pulling the field of the report ID. Instead just put the number that you would like to insert into the database
32 As ReportID
FROM
hospitalcontactreports AS HCR
INNER JOIN
contact
ON
contact.contactid = hcr.contactid
INNER JOIN
hospital
ON
hospital.hospitalid = hcr.HospitalID
INNER JOIN
hospitalgroupreports hgr
ON
hgr.groupreportsid = hcr.groupreportsid
INNER JOIN
ReportGlobal RG
ON
rg.reportid = hgr.reportid
WHERE
--Pick what reportid to use... How you have it you should get an error of Ambiguous column name 'reportid'
reportid = 7
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply