Here you go. This is the sample data from "vvexample(td).txt" (Copy and paste it)
1 1 0 1 1 1 1 0 1 1 1 0 0 0 1 0 0 0 1 1 0 0 1 0 1 0 0 0 0 1 0 1 1 1 0 0 0 1 0 0 1 1 0 1 0
2 1 0 1 1 1 1 0 1 1 1 0 0 0 1 0 0 0 0 0 0 1 1 1 1 1 1 0 1 1 0 0 1 0 0 1 0 1 1 0 1 0 1 1 0
3 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 1 1 0 1 1 0 1 0 1 1 0 1 1 0 0 0 0 0 0 0 0 1 0 1 0 1 1 0 0
4 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 1 1 0 1 0 1 1 0 1 0 0 1 0 0 1 0 1 1 0 0 1 0 0 0 0 0 0 0 1
5 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 0 1 1 1 0 0 1 1 1 0 1 1 0 0 1 0 0 0 1 1 0 1 0 0 1 1 1 0 0
6 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 1 0 0 0 1 0 1 1 0 1 1 1 0 1 0 1 1
7 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 1 1 0 1 1 0 1 0 1 1 0 1 0 1 0 1 1 1 1 0 0 1 0 0 0 1 0 1 1
8 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 1 0 1 1 0 0 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 0 0 0 1 1 0 1
9 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 0 1 1 1 1 0 1 1 1 0 0 1 0 1 1 0 1 0 0 1 1 1 1 1 1 1 1 1 0
10 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 0 0 1 0 0 0 1 1 0 0 1 0 1 0 0 1 1 1 1 1 1 0 0 1 1 1 1 1 1
Mind you, those zeroes should be tab-delimited (td), so go ahead and inject some {tab} in there before trying the next couple of blocks.
Next CREATE a table:
USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_VV_AllExceptOne_](
[45] [int] NULL,
[44] [int] NULL,
[43] [int] NULL,
[42] [int] NULL,
[41] [int] NULL,
[40] [int] NULL,
[39] [int] NULL,
[38] [int] NULL,
[37] [int] NULL,
[36] [int] NULL,
[35] [int] NULL,
[34] [int] NULL,
[33] [int] NULL,
[32] [int] NULL,
[31] [int] NULL,
[30] [int] NULL,
[29] [int] NULL,
[28] [int] NULL,
[27] [int] NULL,
[26] [int] NULL,
[25] [int] NULL,
[24] [int] NULL,
[23] [int] NULL,
[22] [int] NULL,
[21] [int] NULL,
[20] [int] NULL,
[19] [int] NULL,
[18] [int] NULL,
[17] [int] NULL,
[16] [int] NULL,
[15] [int] NULL,
[14] [int] NULL,
[13] [int] NULL,
[12] [int] NULL,
[11] [int] NULL,
[10] [int] NULL,
[09] [int] NULL,
[08] [int] NULL,
[07] [int] NULL,
[06] [int] NULL,
[05] [int] NULL,
[04] [int] NULL,
[03] [int] NULL,
[02] [int] NULL,
[01] [int] NULL
) ON [PRIMARY]
This is ugly to look at but is useful to provide example. Now fill the table:
BULK INSERT [cpqaAnswers].[cpqa].[tbl_VV_AllExceptOne_]
FROM 'C:\Users\VV\vvexample(td).txt'
Note how I'm referencing two things a SCHEMA and a DATABASE ([cpqa] and [cpqaAnswers] ... substitute what you've got where necessary, right? I'll continue to use what I call my example "origin" for ease of use from this end:
USE [cpqaAnswers]
GO
--CREATE SCHEMA [cpqa]
DECLARE @intFixed [int]
SET @intFixed = 2
DECLARE @intCountColumn [int]
SET @intCountColumn = (SELECT count(*) FROM information_schema.columns WHERE table_name = 'tbl_VV_AllExceptOne_')
DECLARE @intVariable [int]
SET @intVariable = 0
DECLARE @strHead [nvarchar](MAX)
SET @strHead = 'SELECT '
DECLARE @strTail [nvarchar](MAX)
SET @strTail = 'FROM [cpqaAnswers].[cpqa].[tbl_VV_AllExceptOne_]'
DECLARE @strConcat [nvarchar](MAX)
SET @strConcat = ''
DECLARE @intLoop [int]
SET @intLoop = 1
DECLARE @strExec [nvarchar](MAX)
SET @strExec = ''
DECLARE @strGrow [nvarchar](MAX)
SET @strGrow = ''
That's the preamble to the WHILE clause, the loop where the stuff happens. This is basic brute force string concatenation:
WHILE @intLoop < @intCountColumn
BEGIN
SET @strConcat = @strHead + '[' + LEFT(REPLICATE('0',@intFixed),@intFixed-LEN(@intLoop))+CAST(@intLoop As [nvarchar](32)) + '] ' + @strTail
SET @strGrow = @strConcat + ',' + @strGrow
SET @intLoop = @intLoop + 1
END
SET @strExec = @strHead + LEFT(@strGrow,LEN(@strGrow)-1) + @strTail
EXECUTE (@strExec)
The requirement which is made really happens at the beginning of the WHILE where @intCountColumn doesn't get the extra "1" added to it to keep @intLoop going until all columns are accounted for in the concatenation @str. Stopping short essentially keeps the return to n-1.