1

I understand how to add a new column and default int to a table.

ALTER TABLE Cards
ADD Amex int NOT NULL DEFAULT(1) // how can I add a random number (between 1, to 10) or random date (jan to feb etc)?
GO

How do I add a new column into a table, and fill it with a random integer between 1 and 10, or a random date?

Edit - Answer from below:

Create table cards (
  Amex int NOT NULL DEFAULT (cast(right(cast(checksum(newid()) as varchar(255)), 1) as int) + 1)
);
aggie
  • 798
  • 2
  • 8
  • 23
  • For random dates, check this post - http://stackoverflow.com/questions/794637/how-to-update-rows-with-a-random-date – kelsier Oct 15 '14 at 08:27

2 Answers2

4

You can do an update. For a number from 1 to 10, the following should work:

update cards
    set Amex = cast(right(cast(checksum(newid()) as varchar(255)), 1) as int) + 1

EDIT:

Here is an example of this being used in a create table statement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What about random dates in a range or out of a list. – aggie Oct 15 '14 at 07:59
  • @aggie . . . You would do something similar with `checksum(newid())`, modulo arithmetic, and start and end dates. The part of the question about dates is much vaguer than the part about integers; plus the title is about integers and not dates. – Gordon Linoff Oct 15 '14 at 16:56
  • tried this -> 'ALTER TABLE Cards ADD Amex int NOT NULL DEFAULT(cast(right(cast(checksum(newid()) as varchar(255)), 1) as int) + 1)' _italic_//sql is giving me errors _italic_ – aggie Oct 15 '14 at 17:08
1

another way for random integer from 0 to 10 in sql-server is:

update cards  
set Amex = CRYPT_GEN_RANDOM(2) % 11

here is a trick for a random month names:

= DATENAME(month, DATEADD(month, (CRYPT_GEN_RANDOM(2) % 13), getdate()))
Mikhail Timofeev
  • 2,169
  • 15
  • 13