Insert Data Into a Wide Table with COLUMN_SET
Posted: August 21, 2015 Filed under: Uncategorized Leave a comment 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