Results 1 to 12 of 12

Thread: SQL Macro question

  1. #1
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default SQL Macro question

    I have a database table where one field requires a Serial Number which is basically a random number between 1 and 999999999

    This is the code I am currently using which works well.

    Code:
    DECLARE @Serial INT;
    DECLARE @Random INT;
    DECLARE @Upper INT;
    DECLARE @Lower INT;
    
    ---- This will create a random number between 1 and 999999999
    SET @Lower = 1 ---- The lowest random number
    SET @Upper = 999999999 ---- The highest random number
    SELECT @Random = ROUND ( ( ( @Upper - @Lower -1 ) * RAND ( ) + @Lower ), 0 );
    SET @Serial = @Random;
    If I want to INSERT data to a table this is ok if I am only doing one INSERT, but I am doing a bulk insert of about 20 items to the table and I want each use @Serial to be a different value

    (This is an extract of just some of the INSERT'ing i am doing.)

    Code:
    INSERT [dbo].[ImportMail]
    ([WorldID],[ToName],[OrgObjID],[Count],[Durable],[ExValue],[ImageObjectID],[Ability],[Title],[Content],[Money],[Money_Account],[Money_Bonus],[GmName],[Serial],[CreateTime]) VALUES
    (1,@ToPlayer,214640,1,@EQDurable,@WEPExValue,214640,@MainWep1H,N'Welcome',N'Hi',0,0,0,N'System',@Serial,@CreateTime),
    (1,@ToPlayer,214639,1,@EQDurable,@WEPExValue,214639,@MainWep2H,N'Welcome',N'Hi',0,0,0,N'System',@Serial,@CreateTime),
    (1,@ToPlayer,212931,1,@EQDurable,@EQExValue,212931,@RangeWep,N'Welcome',N'Hi',0,0,0,N'System',@Serial,@CreateTime),
    (1,@ToPlayer,234068,1,@EQDurable,@EQExValue,234068,@Tally,N'Welcome',N'Hi',0,0,0,N'System',@Serial,@CreateTime);
    How would I make the @Serial be regenerated in this INSERT example above.
    Is it possible as a macro or some function. I have not learned this part in SQL yet. So not sure where or how to tackle it.

    I hope I asked this correctly.
    Last edited by skozzy; 13-11-15 at 12:13 AM.



Look Here ->
  • #2
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    Would a for - next loop not do it. Ensure there is some error trapping to prevent duplicates and away you go.
    pseudo code
    Code:
    declare variable for number of records (numrec)
    For i = 1 to numrec
    set duplicate to T
         while duplicate
                create random number
                insert into record
                if duplicate then
                    loop
                else
                    duplicate = F
               endif
          end while
    next
    I'm out of my mind, but feel free to leave a message...

  • The Following User Says Thank You to lsemmens For This Useful Post:

    skozzy (14-11-15)

  • #3
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default

    I can't see how I can use this for what I was trying to do. In my INSERT each @Serial I want to have a new random number, Guess I can do @Serial1 @Serial2 etc and SET them before the INSERT
    Thanks for the reply too

  • #4
    Senior Member

    Join Date
    Dec 2011
    Location
    Tasmania
    Posts
    869
    Thanks
    1,485
    Thanked 956 Times in 416 Posts
    Rep Power
    544
    Reputation
    18321

    Default

    Unfortunately T-SQL doesn't have a for.. next loop, so you have to build your own using while. This should get you started (note that for demo purposes, I'm only inserting the serial number and nothing else):

    Code:
    DECLARE @Serial INT;
    DECLARE @Upper INT;
    DECLARE @Lower INT;
    
    DECLARE @i INT = 0;
    
    SET @Lower = 1 ---- The lowest random number
    SET @Upper = 999999999 ---- The highest random number
    	
    WHILE @i < 20
    BEGIN
    
    	---- This will create a random number between 1 and 999999999
    	SELECT @Serial = ROUND ( ( ( @Upper - @Lower -1 ) * RAND ( ) + @Lower ), 0 );
    
    	INSERT INTO ImportMail (
    		Serial)
    		VALUES( @serial );
    	
    	SET @i = @i + 1;
    	
    END;
    Not sure where your existing values are coming from though. If they're already in the table, you could just use UPDATE to change/set the serial. If it's coming from another database or table, you could use INSERT INTO.
    Last edited by shred; 14-11-15 at 07:48 AM.

  • #5
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    Thanks Shred, I was not aware that T-SQL did not have a for next loop. @ Skozzy, you are effectively using a "for next" or "while" to achieve what you aim. i.e. if you need to update 20 records then while the record count is below 20 keep updating.
    I'm out of my mind, but feel free to leave a message...

  • #6
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default

    This one table I am working with once data is inserted within a moment the program monitoring that table will process it, so I need to have the new @Serial before all the data is inserted. I thought it might have been possible that each time @Server is referenced it can call on a macro or function or something that will give @Serial a new value each time. It is starting to look like I need to INSERT one line, process a new @Serial and INSERT the next line, doing it in a one bulk INSERT might not be possible.
    For now I have done just that, Generate a new @Serial, INSERT one line, repeat. Looks messy and my original 4kb code is now 20kb. Thats ok, it is still fast enough for inserting 20-30 new lines into that table.
    I was trying also to look at how arrays are done in SQL so I could put my 20-30 lines in an array and process the @Serial that way then use a WHILE loop to INSERT line at a time. But I think the time involved will out weigh the bulky COPY and PASTE of inserting one line at a time. NotePad++ wins here for that task.

    Thanks guys for the ideas. Ive only been at this SQL stuff for a couple of months and learning via google and forums. Getting there bit by bit.

  • #7
    Senior Member

    Join Date
    Dec 2011
    Location
    Tasmania
    Posts
    869
    Thanks
    1,485
    Thanked 956 Times in 416 Posts
    Rep Power
    544
    Reputation
    18321

    Default

    There are a few ways to do what you want properly. I guess it depends if you are doing stuff behind the back of a commercial, shrink wrapped application, or you've written your own app and have full control over how it talks to SQL.

    You haven't said why it's a random number. Does it just need to be a unique number? If so, SQL can handle automatically creating a unique, incrementing value very nicely. In a test database, try setting your int column with the following attributes and see what happens when you insert data:

    • Allow Nulls = false
    • Identity = True
    • Identity Seed = 1
    • Identity Increment = 1


    The first value will be 1, the next inserted row will automatically get the value 2 and so on.

    If you were writing an app from scratch and needed a random number (not a GUID), you might create a stored procedure in the database. When the app wants to insert a row of data, it calls the stored procedure with all the data as parameters to the SP. The SP then checks the data for sanity (no strings in numeric fields and so on), creates the random serial number and performs the actual insert operation.

    The ugly way is to create a trigger in the database that runs some code to update the row with the serial each time it detects an insert - definitely not recommended and a very nasty solution.

  • #8
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    What is the purpose of the serial number? Is it to ensure each record is unique? If you were using M$ Access you could use an auto increment field to do just that? As Shred has indicated, his process will have the same effect. A bit more information on what you are trying to achieve might assist. I.E. Why do you need to perform a "bulk" update? Would it not be far better to complete updating one record before moving to the next? Amending one record at a time would help ensure referential integrity (I assume you database consists of related tables) and also helps if transaction rollback becomes necessary, e.g. power failure before commit. What is the purpose of the database that you are developing?
    I'm out of my mind, but feel free to leave a message...

  • #9
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default

    Its a table in a game database, and It is part of two fields the other is createtime. I havnt seen ill effects having both values at Zero. Was just trying to be complete when it come to filling in the fields in the table. Plus i learn from the experimenting. Like today I worked out why I had problems using PHP to send one of my scripts to the sql server, max_packet default was 4kb, increased that to 32kb and so far my SQL script embedded in my PHP files are working.

  • #10
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    You may be well advised to study up a lot more on database construction because, unless you are bloody lucky, haphazard design will bring you undone down the road. Back in the days of DOS I spent months developing a system that worked fine until "end of year" (not literal EOY) that fell over in a catastrophic way. My problem then was I had missed a "full stop" (.) in one line of code. It took me weeks to find that one. That was in the days of procedural languages. With OO it becomes even more complex, because one change here, may have unintended consequences elsewhere. I've been developing a catalogue program for my stamp collection on and off for about 15years. Starting with early versions of M$ Access. I found out early that Access (against SQL) has a 2Gb Limit on Databases. SQL has no such limit, but there are so many tools and utilities, finding path through them for the "uneducated" can be a minefield.

    What language are you using to develop your app?
    I'm out of my mind, but feel free to leave a message...

  • #11
    Senior Member skozzy's Avatar
    Join Date
    Jan 2008
    Location
    Brisbane
    Age
    55
    Posts
    525
    Thanks
    31
    Thanked 19 Times in 16 Posts
    Rep Power
    216
    Reputation
    114

    Default

    Yeah study and learning is what I am doing, not reckless at all. I have my test database to play with, decent size, learning step by step how it is put together with proceedures, PKs, FKs and a lot more, and getting a grasp on the differences with a few other programming languages I been learning over time.

    Well this game is made by someone else, it just uses MS SQL. The game source in C, many external functions are LUA and XML, and since all games need a home page I also started learning more html, then php and how to use php with the sql database. I had a 20year break from my programming skills when i got a factory job, that is over and so im back to getting my bum flat on a chair. It's just taking longer to learn and harder to remember what I learn.

    With the help of a few mates and help people in forums and many great websites and youtube videos ive been casually getting back in to it. As for Typo-o's I know all about that, my keys like to move from side to side when i'm typing.

  • #12
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    Have Fun.
    I'm out of my mind, but feel free to leave a message...

  • Bookmarks

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •