June 13, 2013 at 6:32 pm
Hi Professionals
I have a procedure that passes in 1:CSV file, 2:Table_name 3:Column_count
The column_count can vary and this is where I am trying to build the mid part of my create table statement within the procedure.
I am iterating through the records and trying to build however many mid columns there needs to be in the create table statement and cannot figure it out. I am almost there with my code.
any ideas professionals, any help would be greatly appreciated
here is my code with output underneath
USE [TestData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[importspreadsheet]
/*Passed in variables from PHP script*/
@importedfile nvarchar(50), /* The csv filename EG test.csv */
@table_name nvarchar(100), /* The tabke name to be created */
@column_count nvarchar(50) /* The total number of columns from spreadsheet */
AS
BEGIN
declare @sql nvarchar(1000),/* The first part of the query build */
@fullquery nvarchar(1000),/* The full joined query */
@sqldrop nvarchar(1000),/* The drop table command */
@intflag int = 0,/* The loop counter for looping through total number of columns minus 1 */
@midcolumns nvarchar(50) = 'colname',/* The middle columns default name which will have intflag variable appended */
@lastcolumn nvarchar(50);/* The last column to close the Create Table query */
--set @sqldrop = 'DROP TABLE dbo.' + quotename(@table_name);
/*Building first columns of the create table statement here which are always going to be there*/
set @sql = 'create table dbo.' + quotename(@table_name, '[')
+ '(software_manufacturer nvarchar(max) null,
product_name nvarchar(max) null,
product_version nvarchar(max) null,';
/*Loop through and create the middle columns based on the column_count variable,
Make sure to do LESS THAN column_count and
NOT LESS THAN OR EQUAL TO so we can close final column outside of loop*/
while (@intflag < @column_count)
begin
--print @intflag
set @intflag = @intflag +1;
set @midcolumns = 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,';
print @midcolumns;
end
/*Create last column in the create table statement*/
set @lastcolumn = 'lastcolumn nvarchar(max) null);';
/*Join the full create statements together to create dynamic table*/
SET @fullquery = (@SQL + @midcolumns + @lastcolumn)
print (@fullquery)
--exec (@sqldrop)
--exec (@sql)
END
colname[2] nvarchar(max) null,
colname[3] nvarchar(max) null,
colname[4] nvarchar(max) null,
colname[5] nvarchar(max) null,
colname[6] nvarchar(max) null,
colname[7] nvarchar(max) null,
colname[8] nvarchar(max) null,
colname[9] nvarchar(max) null,
colname[10] nvarchar(max) null,
colname[11] nvarchar(max) null,
colname[12] nvarchar(max) null,
colname[13] nvarchar(max) null,
colname[14] nvarchar(max) null,
colname[15] nvarchar(max) null,
colname[16] nvarchar(max) null,
colname[17] nvarchar(max) null,
colname[18] nvarchar(max) null,
colname[19] nvarchar(max) null,
colname[20] nvarchar(max) null,
colname[21] nvarchar(max) null,
colname[22] nvarchar(max) null,
colname[23] nvarchar(max) null,
create table dbo.[newtable](software_manufacturer nvarchar(max) null,
product_name nvarchar(max) null,
product_version nvarchar(max) null,colname[23] nvarchar(max) null,lastcolumn nvarchar(max) null);
June 14, 2013 at 3:37 am
It looks like you are missing concatenation
set @midcolumns = @midcolumns + 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,';
As a separate note, I don't think you can create a column name like column[0]
June 14, 2013 at 3:56 am
This might be more efficient for creating the columns also you would need to put the ColNameXX into the square brackets like [ColName1] etc.
This will generate upto 256 columns in ant dataset.
DECLARE @Cols int = 20
DECLARE @ColList varchar(max)
;with L0
AS (SELECT n From (VALUES(1),(1)) b(n)) --2
,L1
AS (SELECT a.n From L0 a CROSS JOIN L0 b) --4
,L2
AS (SELECT a.n From L1 a CROSS JOIN L1 b) --16
,L3
AS (SELECT a.n From L2 a CROSS JOIN L2 b) --265
,CTE_InLineNumbersTable
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Number FROM L3)
select
@ColList= Convert(varchar(max)
,stuff(
(SELECT ', '+Quotename('colname'+convert(varchar,Number),'[')+' varchar(max)'+cHAR(10)
FROM
CTE_InLineNumbersTable
Where Number Between 2 and @Cols
for xml path (''))
,1,1,' '))
Print @ColList
the output here is
[colname2] varchar(max)
, [colname3] varchar(max)
, [colname4] varchar(max)
, [colname5] varchar(max)
, [colname6] varchar(max)
, [colname7] varchar(max)
, [colname8] varchar(max)
, [colname9] varchar(max)
, [colname10] varchar(max)
, [colname11] varchar(max)
, [colname12] varchar(max)
, [colname13] varchar(max)
, [colname14] varchar(max)
, [colname15] varchar(max)
, [colname16] varchar(max)
, [colname17] varchar(max)
, [colname18] varchar(max)
, [colname19] varchar(max)
, [colname20] varchar(max)
Be careful about using the Varchar(MAX) for each column definition.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 16, 2013 at 10:51 pm
thank you that worked a treat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply