I have horizontal table (Table1) and I need it to transform it into (Table2)
Table1:
CEO SALESMAN PRODUCT(1) PRODUCT(2) PRODUCT(3) PRODUCT(4) PRODUCT(5) ... PRODUCT(N)
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
MIKE ANDERSON 76787,00 19388,00 0,00 2723,00 217,00 6581,00
JOHN ANGELA 0,00 0,00 73088,00 0,00 0,00 0,00
JACK JEFF 24716,00 0,00 2995,00 0,00 0,00 0,00
STUART MICHAEL 0,00 23338,00 42656,00 0,00 0,00 0,00
Table2:
CEO SALESMAN PRODUCTS VALUE
------- ----------- ---------- --------
MIKE ANDERSON PRODUCT(1) 76787,00
JOHN ANGELA PRODUCT(1) 0,00
JACK JEFF PRODUCT(1) 24716,00
STUART MICHAEL PRODUCT(1) 0,00
MIKE ANDERSON PRODUCT(2) 19388,00
JOHN ANGELA PRODUCT(2) 0,00
JACK JEFF PRODUCT(2) 0,00
STUART MICHAEL PRODUCT(2) 23338,00
MIKE ANDERSON PRODUCT(3) 0,00
JOHN ANGELA PRODUCT(3) 73088,00
JACK JEFF PRODUCT(3) 2995,00
STUART MICHAEL PRODUCT(3) 42656,00
MIKE ANDERSON PRODUCT(4) 2723,00
JOHN ANGELA PRODUCT(4) 0,00
JACK JEFF PRODUCT(4) 0,00
STUART MICHAEL PRODUCT(4) 0,00
MIKE ANDERSON PRODUCT(5) 217,00
JOHN ANGELA PRODUCT(5) 0,00
JACK JEFF PRODUCT(5) 0,00
STUART MICHAEL PRODUCT(5) 0,00
MIKE ANDERSON ... ...
JOHN ANGELA ... ...
JACK JEFF ... ...
STUART MICHAEL ... ...
MIKE ANDERSON PRODUCT(N) 6581,00
JOHN ANGELA PRODUCT(N) 0,00
JACK JEFF PRODUCT(N) 0,00
STUART MICHAEL PRODUCT(N) 0,00
What I've tried so far is to BULK INSERT Table1 into a temporary table and then work with this data until I get what I want. The problem is the N in PRODUCT() columns is variable so I can't create a temporary table with fixed columns and I need a dynamic query that somehow can read the quantity of products columns and work with it.
BULK INSERT
BULK INSERT #temp
FROM '\\path\file.csv'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR= ';',
ROWTERMINATOR = '\n',
CODEPAGE='RAW'
);
Temporary table:
CREATE TABLE #temp(
col1 varchar(100) null,
col2 varchar(100) null,
col3 varchar(100) null,
col4 varchar(100) null,
col5 varchar(100) null,
...
col397 varchar(100) null,
col398 varchar(100) null,
col399 varchar(100) null,
col400 varchar(100) null
)
When I run the BULK INSERT I get this error:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
This happens because I created a temporary table with fixed 400 columns and there were 127 product columns.
I'm trying to avoid things like these:
DECLARE @NUM_ROWS INT
SET @NUM_ROWS = 123
IF @NUM_ROWS = 1
BEGIN
CREATE TABLE #temp(
col1 varchar(100) null
)
END
IF @NUM_ROWS = 2
BEGIN
CREATE TABLE #temp(
col1 varchar(100) null,
col2 varchar(100) null,
)
END
...
IF @NUM_ROWS = 400
BEGIN
CREATE TABLE #temp(
col1 varchar(100) null,
col2 varchar(100) null,
...
col4 varchar(100) null,
)
END
BULK INSERT #temp
FROM '\\path\file.csv'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR= ';',
ROWTERMINATOR = '\n',
CODEPAGE='RAW'
);
@NUM_ROWS would be the number of columns I would create the temp table.
Does anyone know a way I can import this .csv file into SQL Server dynamically? By dynamically I mean creating a temporary table with the number of columns of PRODUCTS I'm bulk inserting. Or a workaround to avoid that error I posted above.
Here's a somewhat manual approach:
From here on, it's all SQL:
Insert into Table2 Select CEO, Salesmen, 1 as ProductNum, [Product(1)] from Table1 where [Product(1)] is not null union Select CEO, Salesmen, 2 as ProductNum, [Product(2)] from Table1 where [Product(2)] is not null ... union Select CEO, Salesmen, 400 as ProductNum, [Product(400)] from Table1 where [Product(400)] is not null
Sure, 400 lines of this would be ugly, but you can easily read it and count from 1 to 400.