I am looking to define data types by fields in SQL Server 2005. My source is an Excel spreadsheet and I cannot define by columns because each row requires a set of definitions applied to each field in that row. Ex, row 1 requires field1 to be CHAR while in row 2 field 1 needs to be DATE. Any suggestions? Thanks in advance.
-
2What does "Field" mean in the context you're using it? Are you referring to an Excel "cell"? SQL Server requires that a column be a specific datatype; you can't have one row of the column be a date and the next row of that same column be an integer, unless you convert each of them into a common data type (for example VarChar).Ken White– Ken White2011-05-05 18:07:02 +00:00Commented May 5, 2011 at 18:07
Add a comment
|
1 Answer
You can use a sql_variant column to store any data type. It will allow Excel dates, strings, and numbers to be stored in the same SQL Server column. A sql_variant column can contain a values of any SQL data type with no loss of precision.
CREATE TABLE Sheet
(
RowNumber int NOT NULL,
ColumnLetter varchar(5) NOT NULL,
FieldValue sql_variant NULL,
PRIMARY KEY (RowNumber, ColumnLetter)
)
INSERT Sheet SELECT 1, 'A', 'Values'
INSERT Sheet SELECT 2, 'A', GETDATE()
INSERT Sheet SELECT 3, 'A', 123
INSERT Sheet SELECT 4, 'A', 123.00
