Help Please! - Using Sparse Column Set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help Please! - Using Sparse Column Set

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-05-17 : 06:51:23

I have a csv that I need to import to a table, the problem is the number of columns in the csv is 1282 which is greater than the number of columns I can create a table for.

Now i have read up a little on creating a table with Sparse columns which I have duly done (cut down version shown)


CREATE TABLE [dbo].[mytable_Sparse](
	[Id] [varchar](50) SPARSE  NULL,
	[NewID] [varchar](50) SPARSE  NULL,
	[RfA] [varchar](max) SPARSE  NULL,
	[CType] [varchar](50) SPARSE  NULL,
	[P_Id] [varchar](50) SPARSE  NULL,
	[Identifier] [varchar](50) SPARSE  NULL,
	[UpdateType] [varchar](50) SPARSE  NULL,
	[BulkN] [varchar](50) SPARSE  NULL,
	[TInd] [varchar](50) SPARSE  NULL,
        [cs] [xml] COLUMN_SET FOR ALL_SPARSE_COLUMNS  NULL
) ON [PRIMARY]

Now i come to my little problem.

How do I (Bulk?) import the csv into this new table, I've googled for hours and can't seem to find anything specific to Bulk inserting from a csv into a Sparse column Set table.

Can anyone help please, some examples would be good.

Thanks.

B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-17 : 07:10:34

You must specify all columns individually.

Or... Import everything into one column and then use the function fnParseString here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 to extract column by column, with a WHILE loop.

DECLARE @ColNum SMALLINT = -1282

WHILE @ColNum <= 1 -- Iterate all column from left to right
    BEGIN
        SELECT  dbo.fnParseString(@ColNum, ',', DataCol)
        FROM    dbo.Table

        SET @ColNum += 1
    END

N 56°04'39.26"
E 12°55'05.63"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2012-05-17 : 08:08:12

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources