Вставка случайных имен в качестве значений SQL

Итак, я создал базу данных, представляющую собой банк с клиентами; но в этой базе данных я написал функцию, которая возвращает 50 клиентов (или, в моем случае, 50 Джонов Смитов)

...Потому что я жестко закодировал имя Джона Смита и его адрес электронной почты моего клиента в этом коде. Теперь я хочу найти способ заменить имя, которое я жестко закодировал, переменной для имени и фамилии, которая будет возвращать только случайные имена. Как Джеймс Доу, Салли Филдс и т. д.

Пожалуйста, укажите мне правильное направление, которое правильно заменит Джона Смита случайными именами.

Вот код моей функции, чтобы вы поняли, о чем я говорю и что я ищу.

BEGIN
     DECLARE @loopCount tinyint 
     DECLARE @tableCount tinyint
     DECLARE @randomSSN int
     SELECT @loopCount = 1
     WHILE (@loopCount <= 50)
        BEGIN
            SELECT @randomSSN = RAND()*(999999998)+1
            SELECT @tableCount = COUNT(*) FROM Customer WHERE SSN = @randomSSN
            IF @tableCount = 0
                 INSERT INTO Customer (FirstName, LastName, Email, SSN) VALUES ('John', 'Smith', '[email protected]', @randomSSN)
                SELECT @loopCount = @loopCount +1
        END
END

random sql-server names
person Ross Chris    schedule 05.11.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

вы можете использовать что-то вроде этого

BEGIN
 DECLARE @loopCount tinyint 
 DECLARE @tableCount tinyint
 DECLARE @randomSSN int
 SELECT @loopCount = 1
 WHILE (@loopCount <= 50)
    BEGIN
        SELECT @randomSSN = RAND()*(999999998)+1
        SELECT @tableCount = COUNT(*) FROM Customer WHERE SSN = @randomSSN
        IF @tableCount = 0

             WITH cte as (
                  SELECT *
                   FROM (VALUES ('Ann','smith'),('Sarah','Paulson'),('Phil','Coulson'),('John','Palmer'),
                                ('Steve','Jobs'),('Bill','Gates')) AS t(first_name,Last_name)
                  )
             INSERT INTO Customer (FirstName, LastName, Email, SSN) 
             VALUES (
             (select top 1 first_name from cte order by newid()), 
             (select top 1 Last_name from cte order by newid()), 
             '[email protected]', 
              @randomSSN)

            SELECT @loopCount = @loopCount +1
    END
END
person Tanveer Singh Bhatia    schedule 05.11.2018

arrow_upward
1
arrow_downward

Во-первых, вам нужно создать набор имен, которые будут использоваться

  CREATE TABLE [SampleData] 
   ( [Id] INTEGER NOT NULL IDENTITY(1, 1),
     [FirstName] VARCHAR(255) NULL,
     [LastName] VARCHAR(255) NULL,
     [Email] VARCHAR(255) NULL,
     PRIMARY KEY ([SampleDataID]) );

 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Hop','Elijah','[email protected]'),('Darius','Aquila','[email protected]'),('Matthew','Murphy','[email protected]'),('Myles','Ross','[email protected]'),('Zephania','Martin','[email protected]'),('Wang','Hammett','[email protected]'),('Jameson','Christopher','[email protected]'),('Nicholas','Abdul','[email protected]'),('Clark','Cyrus','[email protected]'),('Sebastian','Octavius','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Hu','Zeph','[email protected]'),('Clark','Caleb','[email protected]'),('Cadman','Allistair','[email protected]'),('Mark','Michael','[email protected]'),('Nehru','Jelani','[email protected]'),('Carlos','Brody','[email protected]'),('Wayne','Joel','[email protected]'),('Seth','Malik','[email protected]'),('Adam','Rooney','[email protected]'),('Lucian','Zahir','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Jerry','Evan','[email protected]'),('Victor','Micah','[email protected]'),('Reuben','Connor','[email protected]'),('Coby','Stuart','[email protected]'),('Raja','Calvin','[email protected]'),('Linus','Honorato','[email protected]'),('Felix','Octavius','[email protected]'),('Stephen','Preston','[email protected]'),('Andrew','Arsenio','[email protected]'),('Alan','Eagan','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Elmo','Igor','[email protected]'),('Nicholas','Burton','[email protected]'),('John','Fletcher','[email protected]'),('Cyrus','Noble','[email protected]'),('Gary','Noble','[email protected]'),('Ira','Darius','[email protected]'),('Chadwick','Octavius','[email protected]'),('Ethan','Keaton','[email protected]'),('Reuben','Kenyon','[email protected]'),('Ian','Erich','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Yoshio','Ezekiel','[email protected]'),('Nero','Cedric','[email protected]'),('Shad','Conan','[email protected]'),('Yardley','Julian','[email protected]'),('Lawrence','Caleb','[email protected]'),('Jacob','Wallace','[email protected]'),('Barry','Reuben','[email protected]'),('Erich','Bruce','[email protected]'),('Charles','Damian','[email protected]'),('Noah','Buckminster','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Porter','Randall','[email protected]'),('Troy','Grady','[email protected]'),('Neil','Abdul','[email protected]'),('Lane','Phelan','[email protected]'),('Sylvester','Keegan','[email protected]'),('Patrick','Jackson','[email protected]'),('Jesse','Dillon','[email protected]'),('Myles','Xavier','[email protected]'),('Kasimir','Ray','[email protected]'),('Noble','Judah','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Omar','Wayne','[email protected]'),('James','Patrick','[email protected]'),('Damian','Hasad','[email protected]'),('Lucius','Leroy','[email protected]'),('Uriel','Amos','[email protected]'),('Eric','Benedict','[email protected]'),('Stephen','Harlan','[email protected]'),('Fitzgerald','Nathaniel','[email protected]'),('Ishmael','Logan','[email protected]'),('Keefe','Dylan','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Dennis','Aquila','[email protected]'),('Dieter','George','[email protected]'),('Kane','Ronan','[email protected]'),('Jameson','Edward','[email protected]'),('Hamish','Fuller','[email protected]'),('Elliott','Levi','[email protected]'),('Michael','Thomas','[email protected]'),('Hoyt','Coby','[email protected]'),('Cedric','Tad','[email protected]'),('Amal','Vernon','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Addison','Craig','[email protected]'),('Malik','Micah','[email protected]'),('Josiah','Cedric','[email protected]'),('Hayes','Blaze','[email protected]'),('James','Henry','[email protected]'),('Thaddeus','Cairo','[email protected]'),('Nathaniel','Ishmael','[email protected]'),('Wade','Kenyon','[email protected]'),('Gabriel','Kenyon','[email protected]'),('Gavin','Michael','[email protected]');
 INSERT INTO SampleData([First_Name],[Last_Name],[Email]) VALUES('Devin','Emery','[email protected]'),('Dean','Herrod','[email protected]'),('Zachery','Bernard','[email protected]'),('Abdul','Dexter','[email protected]'),('Norman','Tad','[email protected]'),('Eaton','Colin','[email protected]'),('Christian','Daniel','[email protected]'),('Harding','Daniel','[email protected]'),('Matthew','Cullen','[email protected]'),('Charles','Erich','[email protected]');

Теперь мы можем просто выбрать случайные имена из созданного списка

  INSERT INTO Customer (FirstName, LastName, Email, SSN) 
  SELECT FirstName,LastName,Email, RAND()*(999999998)+1 
  FROM SampleDate
  WHERE Id=CAST(RAND()*100 as int)%50
person Sanal Sunny    schedule 05.11.2018