Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

TSQL question, generating an incrementing column which is part string...

Options
  • 13-11-2013 7:28pm
    #1
    LordSinclair
    Closed Accounts Posts: 83 ✭✭


    Hi Folks,

    Would anyone know how to generate a table with two columns using TSQL, one is an identity column and the other column needs to be a list in the following format


    1ABC
    2ABC
    3ABC
    4ABC
    5ABC

    Etc, etc, etc...

    I'm trying to upgrade my site this week and need to databind a GridView dropdown list to this table on Edit mode, I have everything hooked up apart from the table which needs to end up with 300ABC, am hoping someone might have the anbswer to this as I'll be all night creating this table by inputting the values manually into my MS SQL DB!

    Thanks in advance for any help with this and apologies for appearing lazy, am just hunkered down in another end of this site upgrade and have never really used TSQL before...

    EDIT: I've created rows 1-10 manually just to let me get on with things...


Comments

  • Options
    #2
    Sean^DCT4
    Registered Users Posts: 527 ✭✭✭Sean^DCT4


    A SQL trigger on each record that is inserted in to the DB ?

    Something like..
    CREATE trigger TriggerNameHere on YourTableNameHere for insert
AS
DECLARE @idColumn INT = (SELECT Id from inserted);
DECLARE @textColumn NVARCHAR(20) = (SELECT TextColumn from inserted);

UPDATE YourTableNameHere SET MergedColumn = CAST(@idColumn as NVARCHAR(10)) + @textColumn WHERE ID = @idColumn

    So, each time a record is created your new composite column will be populated with the ID and Text value as you wanted it.

    You should note that you will need to modify the SQL above to handle updates too. Shouldn't be a problem as it's pretty much done for you.


  • Options
    #3
    LordSinclair
    Closed Accounts Posts: 83 ✭✭LordSinclair


    Sean^DCT4 wrote: »
    A SQL trigger on each record that is inserted in to the DB ?

    Something like..
    CREATE trigger TriggerNameHere on YourTableNameHere for insert
AS
DECLARE @idColumn INT = (SELECT Id from inserted);
DECLARE @textColumn NVARCHAR(20) = (SELECT TextColumn from inserted);

UPDATE YourTableNameHere SET MergedColumn = CAST(@idColumn as NVARCHAR(10)) + @textColumn WHERE ID = @idColumn
    So, each time a record is created your new composite column will be populated with the ID and Text value as you wanted it.

    You should note that you will need to modify the SQL above to handle updates too. Shouldn't be a problem as it's pretty much done for you.

    Hi Sean, no I just need to bind a DropDownList, back to a table with 300 values in the format above, as a "once off" excercise. The table won't be getting changed after it is built, it is only being used as a list of options for an update event for a different table if you get what I mean...


  • Options
    #4
    colm_c
    Registered Users Posts: 2,030 ✭✭✭colm_c


    A quick 300 inserts via direct SQL shouldn't take long, failing that, a ruby, php, bash etc script would do the job.


  • Options
    #5
    Colonel Panic
    Registered Users Posts: 2,019 ✭✭✭Colonel Panic


    Sean's trigger wouldn't workforce multiple records in one insert anyway.

    Struggling to understand why you don't just use an update to set all your text columns in one go. That's part of the point of SQL.

    Or since it's just a load of inserts, batch it.


  • Options
    #6
    kayos
    Registered Users Posts: 2,494 ✭✭✭kayos


    If the table exists then how about a computed column? Even if you have to create a new table I am guessing you need to populate it from an existing table for the string values. You could just insert @identity + text into the desired column.

    Just not fully clear on what you really need here.


  • Advertisement
  • Options
    #7
    Procasinator
    Registered Users Posts: 1,311 ✭✭✭Procasinator


    I'm not sure I get what you want to do. Something like this: 
    DECLARE @num int = 1
WHILE @num <= 300
BEGIN
	INSERT INTO TableName (Code) VALUES (CONVERT(nvarchar, @num) + 'ABC')
	SET @num = @num + 1
END

    In this code I assume the identity column is taken care of automatically.


  • Options
    #8
    LordSinclair
    Closed Accounts Posts: 83 ✭✭LordSinclair


    Procasinator wrote: »
    I'm not sure I get what you want to do. Something like this: 
    DECLARE @num int = 1
WHILE @num <= 300
BEGIN
    INSERT INTO TableName (Code) VALUES (CONVERT(nvarchar, @num) + 'ABC')
    SET @num = @num + 1
END
    In this code I assume the identity column is taken care of automatically.

    Thanks a mil for that Procrastinator, saved me 600 manual inserts, I know this was a basic ask but when you are bogged down in a whole other area of a development, you don't wanna have to go back to your software eng bible!


Advertisement