April 24, 2012 at 10:37 am
/****** Script for SelectTopNRows command from SSMS ******/
INSERT INTO dbo.SMARTAdmissions ([SMARTUploadID]
,[Admission ID]
,[Treatment Services Group ID]
,[Client Intake ID]
,[Client ID]
,[Race Group ID]
,[Agency ID]
,[Clinic ID]
,[National Provider Identifier]
,[Facility Identifier]
,[Unique Client ID]
,[Admission Creation Date]
,[Agency Client ID]
,[Social Security Number]
,[Client Type]
,[Date of Admission]
,[Transaction Type]
,[Number of Prior Admissions]
,[Source of Referral]
,[Sex]
,[Race]
,[Ethnicity]
,[Date of Birth]
,[County of Residence]
,[Zip Code]
,[Marital Status]
,[Highest School Grade Completed]
,[Employment Status]
,[Family Income]
,[Primary Source of Income]
,[Living Arrangement]
,[Number of Dependant Children]
,[Health Coverage]
,[Currently Pregnant?]
,[Current Mental Health Problems?]
,[Tobacco use in the past 30 days?]
,[Number of days waiting to enter treatment]
,[ASI Medical Score]
,[ASI Employment Score]
,[ASI Alcohol Score]
,[ASI Drug Score]
,[ASI Legal Score]
,[ASI Family Score]
,[ASI Psychiatric Score]
,[In a Controlled Environment past 30 Days?]
,[POSIT Substance Abuse Score]
,[POSIT Physical Health Score]
,[POSIT Mental Health Score]
,[POSIT Family Score]
,[POSIT Peer Score]
,[POSIT Education Status Score]
,[POSIT Vocational Status Score]
,[POSIT Social Skill Score]
,[POSIT Leisure Recreatal Score]
,[POSIT Aggression Score]
,[POSIT STD HIV Risk Score]
,[Primary Substance]
,[Primary Severity]
,[Primary Frequency]
,[Primary Route]
,[Primary Age of First Use]
,[Secondary Substance]
,[Secondary Severity]
,[Secondary Frequency]
,[Secondary Route]
,[Secondary Age of First Use]
,[Tertiary Substance]
,[Tertiary Severity]
,[Tertiary Frequency]
,[Tertiary Route]
,[Tertiary Age of First Use]
,[Treatment Setting]
,[Attending Grades K-12]
,[Attending GED Program]
,[Attending Vocational Training]
,[Attending Higher Education]
,[Number of Arrests in the Past Year]
,[Number of Arrests in the Past 30 Days]
,[Special Funding 1]
,[Special Funding 2]
,[Special Funding 3]
,[Special Project 1]
,[Special Project 2]
,[Special Project 3]
,[Number of Days in Support Group in Last 30 Days]
,[Number of Days Attended AA/NA in Last 30 Days]
,[Additional Information]
,[Is Submit Without ASI Scores]
,[Intake Date]
,[Intake Creation Date]
,[IFB]
,[HATSAdmissionID]
,[SrcFileDate])
SELECT A.[ID]
,CAST(A.[Admission ID] AS INT)
,C.ID
,CAST(A.[Client Intake ID] AS INT)
,CAST(A.[Client ID] AS INT)
,B.ID
,CAST(A.[Agency ID] AS INT)
,CAST(A.[Clinic ID] AS INT)
,CAST(A.[National Provider Identifier] AS INT)
,CAST(A.[Facility Identifier] AS INT)
,A.[Unique Client ID]
,A.[Admission Creation Date]
,A.[Agency Client ID]
,A.[Social Security Number]
,A.[Client Type]
,A.[Date of Admission]
,A.[Transaction Type]
,A.[Number of Prior Admissions]
,A.[Source of Referral]
,A.[Sex]
,A.[Race]
,A.[Ethnicity]
,A.[Date of Birth]
,A.[County of Residence]
,A.[Zip Code]
,A.[Marital Status]
,A.[Highest School Grade Completed]
,A.[Employment Status]
,A.[Family Income]
,A.[Primary Source of Income]
,A.[Living Arrangement]
,A.[Number of Dependant Children]
,A.[Health Coverage]
,A.[Currently Pregnant?]
,A.[Current Mental Health Problems?]
,A.[Tobacco use in the past 30 days?]
,A.[Number of days waiting to enter treatment]
,A.[ASI Medical Score]
,A.[ASI Employment Score]
,A.[ASI Alcohol Score]
,A.[ASI Drug Score]
,A.[ASI Legal Score]
,A.[ASI Family Score]
,A.[ASI Psychiatric Score]
,A.[In a Controlled Environment past 30 Days?]
,A.[POSIT Substance Abuse Score]
,A.[POSIT Physical Health Score]
,A.[POSIT Mental Health Score]
,A.[POSIT Family Score]
,A.[POSIT Peer Score]
,A.[POSIT Education Status Score]
,A.[POSIT Vocational Status Score]
,A.[POSIT Social Skill Score]
,A.[POSIT Leisure Recreatal Score]
,A.[POSIT Aggression Score]
,A.[POSIT STD HIV Risk Score]
,A.[Primary Substance]
,A.[Primary Severity]
,A.[Primary Frequency]
,A.[Primary Route]
,A.[Primary Age of First Use]
,A.[Secondary Substance]
,A.[Secondary Severity]
,A.[Secondary Frequency]
,A.[Secondary Route]
,A.[Secondary Age of First Use]
,A.[Tertiary Substance]
,A.[Tertiary Severity]
,A.[Tertiary Frequency]
,A.[Tertiary Route]
,A.[Tertiary Age of First Use]
,A.[Treatment Setting]
,A.[Attending Grades K-12]
,A.[Attending GED Program]
,A.[Attending Vocational Training]
,A.[Attending Higher Education]
,A.[Number of Arrests in the Past Year]
,A.[Number of Arrests in the Past 30 Days]
,A.[Special Funding 1]
,A.[Special Funding 2]
,A.[Special Funding 3]
,A.[Special Project 1]
,A.[Special Project 2]
,A.[Special Project 3]
,A.[Number of Days in Support Group in Last 30 Days]
,A.[Number of Days Attended AA/NA in Last 30 Days]
,A.[Additional Information]
,A.[Is Submit Without ASI Scores]
,A.[Intake Date]
,A.[Intake Creation Date]
,A.[IFB]
,A.[HATSAdmissionID]
,A.[SrcFileDate]
FROM [SMART].[dbo].[SMARTAdmissionUpload] A, SMART.dbo.Admissions_Race_Group B, SMART.dbo.Admissions_Treatment_Services_Group C
April 24, 2012 at 10:42 am
And the question?
April 24, 2012 at 10:44 am
the question is that i don't know why I am still getting conversion error mesage: Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'MD-101289' to data type int.
April 24, 2012 at 10:46 am
You're trying somewhere in that huge column list to insert a varchar value into an int column. Check the types of each columns. Or could be that one of those CAST (<column name> AS INT) you have there is trying to cast a column that is not integer to an integer. Check the contents of the columns.
Is that supposed to be a cross join (cartesian product) there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 10:47 am
deebabat (4/24/2012)
the question is that i don't know why I am still getting conversion error mesage: Msg 245, Level 16, State 1, Line 2Conversion failed when converting the varchar value 'MD-101289' to data type int.
Because you can't convert the string value 'MD-101289' to an int. Not sure what else to say here.
April 24, 2012 at 10:48 am
What’s so difficult in the error message? Isn’t it sufficient to explain, you are trying to convert non-qualifying numeric value to integer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply