asked
Inserting into tables
Posted - 03/25/2015 : 17:33:22 Show Profile Email Poster Edit Topic Reply with Quote I have more than 720000 lines when I execute the following sql, There are only 2200 of the 'name' but each name associated with multiple code/levelcode etc. Is there a way that I can insert one code/LevelCode/HL7Code to each 2000 names ? --------------------------------------------------------------------- SELECT DISTINCT ocmi.name [name] , cit.active [taskactive] , Code , LevelCode , HL7Code FROM cv3ordercatalogmasteritem ocmi LEFT JOIN cv3catalogitemtask cit ON ocmi.guid = cit.ordercatalogmasteritemguid LEFT JOIN CV3CatalogTaskLocationXRef tasklocxref ON cit.guid = tasklocxref.CatalogItemTaskGUID JOIN cv3location l ON l.guid = tasklocxref.LocationGroupGUID WHERE ocmi.active = 1 AND ocmi.name NOT LIKE 'zz%' AND ocmi.name NOT LIKE 'qq%' AND ocmi.name <> 'refill' AND ocmi.name NOT LIKE 'qa%' AND ocmi.name NOT LIKE 'Saline Lock%' /* select ocmi.name from cv3ordercatalogmasteritem ocmi left JOIN CV3OrganizationalUnit ou ON ocmi.orgunitguid = ou.guid where ou.name = 'Pharmacy' and ou.active=1 and ocmi.name not like 'zz%' and ocmi.name not like 'qq%' and ocmi.name <> 'SK RX UOMTEST' and ocmi.name <> 'SKStylenol' and ocmi.name <> 'refill' and ocmi.name not like 'qa%' and ocmi.active=1*/ ORDER BY ocmi.Name
The way to figure out if you can do the INSERT is to first figure out how to query the appropriate data using a SELECT. Once you have that figured out, you just use an INSERT ... SELECT ... statement to complete the data in the manner you're looking for.
So you're pivoting 375 rows into one row? Check out the PIVOT operator
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
15 People are following this question.
