here's the idea behind Dynamic SQL:
The user will be passing table name to make changes to it, as they past table name, I'm creating another table in the same structure as the as the one they passed in, leaving out the ID IDENTITY(1,1), I do that with Dynamic SQL.
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity= 0
ORDER BY column_id;
the next step is to load all of the data from the table they passed in as a parameter to the new table I created, but leaving out the ID IDENTITY(1,1). That's where I'm getting an error.