CODEWITHSUNDEEP
sqlvb.netsql-server-2005visual-studio-2005
Wence
Wence

Reputation:

Defining Data Types in SQL Server by Fields not Columns

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.

Upvotes: 1

Views: 453

Answers (1)

Anthony Faull
Anthony Faull

Reputation: 17957

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

Upvotes: 1

Related Questions