How do I insert a row into a table where one column has the next character on the ascii table for the next 11 available characters?

I am new at SQL (know enough to be dangerous but not enough to do elegant code) I am working with an existing database and they had used ascii characters as unique identifiers.For example:

"   Note
$   Sales Call
A   Call Back
B   Busy
C   Call Back - Appointment
D   test
E   Example Call Status
h   dafs
L   Left Message
M   New Call

I am trying to find a way to write a script that will add the next row to a table that has the next available ascii character. Due to the nature of the database, I cannot change how it is set up.

I have tried Googling things like:

sql i need to create a row with the next character in ascii
sql add next available character
sql select next ascii character

Most of the results are to just see the next ascii character.

I have tried finding a way of just creating a temp table of all the ascii characters and then removing the ones that already exist, filling in the 11 rows with the information that needs to be added then stop when the next row would have a null for the description.

I found posts like create a list of the alphabet via SQL

But still cannot figure out how to add to the existing or create the temp table.

Begin transaction

Create Table #asciitemp (
status  char,
statusdesc  varchar,
resolved    bit,
defstat bit,
notchange   bit,
backcolor   int,
forecolor   int,
timestamp   timestamp,
autocall    smallint,
statusdescF varchar,
statusdescS varchar,
handheld    bit
)

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 256 
Set @statusascii CHAR(1) = 'A'

BEGIN   

Set @statusasciichar CHAR(1) = @statusascii CHAR(1) + 1

Insert into #asciitemp 
Value (@statusasciichar, Null, Null, Null, Null, Null, Null, timestamp, Null, Null, Null, Null)

END  

Deleted from #asciitemp 
where #asciitemp.status = #CallStatus.status

–I know that this is EXTREMELY novice and messy but I am trying to piece it together with chunks of code from various sources on the internet.

Ideally I would like to be able to add the rows directly to the table but am willing to add the information to a temp table and then copy it to the actual table. Outside of brute forcing it by creating a table will all the ascii values and deleting the ones that already exist, I am at a loss.

Answer

How does this work for you? May not be a complete answer but it’s a start (and I can update this post accordingly.)

WITH 
E1(N)     AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E1, E1 a, E1 b)
Insert into #asciitemp 
SELECT TOP (255) 
CHAR(i.N), Null, Null, Null, Null, Null, Null, NULL, Null, Null, Null, Null
--i.N, C = CHAR(i.N)
FROM iTally AS i;

Another way (using a temp table for a demo you can copy/paste run)…

DECLARE @table TABLE (N INT IDENTITY, somecol BIT, C AS CHAR(N));

INSERT @table (somecol)
SELECT TOP (255) 1
FROM sys.all_columns;

-- compare results
SELECT t.* FROM @table AS t;

Advertisement

Answer