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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy