Insert Data Into a Wide Table with COLUMN_SET

A nonwide table in SQL Server can have 1024 columns and a wide table can have 30000 columns with Column_SET and SPARSE column design.
Here is a working sample to demonstrate how to insert data into a wide table.

  

 if object_id('dbo.widetabletest','U') is not null
 drop table widetabletest ;

 CREATE TABLE [dbo].[widetabletest](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[col0] [varchar](50) NULL,
	[c1col1] [varchar](50) SPARSE  NULL,
	[c2col1] [varchar](50) SPARSE  NULL,
	[c3col1] [varchar](50) SPARSE  NULL,
	[c15000col1] [varchar](50) SPARSE  NULL,
	[c15000Xmlcol1] [xml] COLUMN_SET FOR ALL_SPARSE_COLUMNS  NULL)
 
 
 INSERT widetabletest (col0, [c15000Xmlcol1])
VALUES ('row1', '<c1col1>test1</c1col1><c3col1>abc</c3col1><c15000col1>Something1</c15000col1>');
INSERT widetabletest (col0, [c15000Xmlcol1])
VALUES ('row2', '<c1col1>test2</c1col1><c2col1>test2!!!!</c2col1><c15000col1>Something1</c15000col1>');


 --create a format file DECLARE @cmd VARCHAR(4000) 
 DECLARE @cmd NVARCHAR(4000) 
set @cmd ='bcp   [test1].[dbo].[widetabletest] format nul -c -x -f  C:\temp\myFormatFileTest.xml -T  -t\t -S'+ @@servername;
exec master..xp_cmdshell @cmd
 
 Go

---generate a test file
DECLARE @cmd6 NVARCHAR(4000) 
set @cmd6 = 'BCP [test1].[dbo].[widetabletest] OUT "C:\temp\myDataWidetable.txt"   -f  "C:\temp\myFormatFile2.xml" -T  -t\^|  -S'+ @@servername
exec master..xp_cmdshell @cmd6

Go
/*
Use any of the below methods:  
bcp;
Bulk insert ;
Insert with OPENROWSET

*/
 ----bcp in with all data
 DECLARE @cmd8 NVARCHAR(4000) 
set @cmd8 = 'BCP  [test1].[dbo].[widetabletest] IN "C:\temp\myDataWidetable.txt"  -f  "C:\temp\myFormatFile2.xml"  -T -S '+ @@servername
exec master..xp_cmdshell @cmd8
 
go

BULK INSERT [test1].[dbo].[widetabletest] 
   FROM 'C:\temp\myDataWidetable.txt'
    WITH (FORMATFILE = 'C:\temp\myFormatFile2.xml');


--Bulk insert
  INSERT INTO [test1].[dbo].[widetabletest] ([col0],[c15000Xmlcol1])
    SELECT [col0],[c15000Xmlcol1]
      FROM  OPENROWSET(BULK  'C:\temp\myDataWidetable.txt',
      FORMATFILE='C:\temp\myFormatFile2.xml'  
      ) as t1 ;
 

 --Check
 Select * from widetabletest
 

Advertisements

---