MS SQL Server
  1. MS SQL Server
  3. Générateur de nom random SQL n'insérant pas le prénom et le nom dans la même ligne
Intereting Posts
Comment merge plusieurs lignes dans une seule ligne server SQL sélectionner à partir de deux tables où les colonnes ne sont pas égaux Comment réduire le time d'exécution des requêtes SQL Fonction scalaire déterministe pour get la semaine de l'année pour une date SQL: spécifiez quelle table un nom de colonne fait reference à Valeurs de tableau écrites sur SQL Server Comment find toutes les procédures stockées qui utilisent une fonction spécifique SQL-COUNT toutes les lignes avec la condition WHERE dans toutes les tables de la database Comment merge ces 2 requêtes Créer une table mysql avec TIMESTAMPDIFF Avoir une fonction table dans la table de return T-SQL avec un nombre variable de colonnes SQL Server Scheduled Job – dit son fonctionnement sans erreurs, mais ne rien faire Utilisation appropriée d'une instruction Case nestede dans une instruction de requête SQL Créer un type de table défini par l'user SQL Server en fonction d'une table Comment puis-je créer une procédure stockée dans plusieurs instances dans un script go / one?

Générateur de nom random SQL n'insérant pas le prénom et le nom dans la même ligne

J'ai réussi à créer une requête simple qui sélectionne un prénom et un nom au hasard et les insère dans une table de résultat. Je voulais créer quelque chose que je pourrais échanger avec les différents tests que je fais pour produire beaucoup de données. Voici le code (je n'ai inclus que 5 prénoms et noms à des fins de simplicité): 

SELECT FirstName, LastName FROM (SELECT TOP 1 FirstName FROM (SELECT 'John' AS FirstName UNION SELECT 'Tim' AS FirstName UNION SELECT 'Laura' AS FirstName UNION SELECT 'Jeff' AS FirstName UNION SELECT 'Sara' AS FirstName) AS First_Names ORDER BY NEWID()) n1 FULL OUTER JOIN (SELECT TOP 1 LastName FROM (SELECT 'Johnson' AS LastName UNION SELECT 'Hudson' AS LastName UNION SELECT 'Jackson' AS LastName UNION SELECT 'Ranallo' AS LastName UNION SELECT 'Curry' AS LastName) AS Last_Names ORDER BY NEWID()) n2 ON [n1].FirstName = [n2].LastName WHERE n1.FirstName IS NOT NULL OR n2.LastName IS NOT NULL

Voici les résultats: 

 FirstName LastName NULL Hudson John NULL

Je souhaite que les résultats returnnent une ligne avec un prénom et un nom générés randomment afin que chaque ligne ait un nom complet (pas de valeurs NULL). Je suis sûr que c'est quelque chose de simple que je néglige.

Le code suivant vous permettra de générer une série de noms randoms, où une solution de jointure croisée ne permet qu'un seul à la fois. Vous ne savez pas si vous devez le faire plus d'une fois, mais si vous le faites: 

 create table #table (firstname varchar(50), lastname varchar(50)) declare @counter int = 1 declare @max int = 5 --set number of repetitions here declare @a varchar(50) declare @b varchar(50) while @counter <= @max begin SET @a = (SELECT TOP 1 FirstName FROM (SELECT 'John' AS FirstName UNION SELECT 'Tim' AS FirstName UNION SELECT 'Laura' AS FirstName UNION SELECT 'Jeff' AS FirstName UNION SELECT 'Sara' AS FirstName) AS First_Names ORDER BY NEWID()) SET @b = (SELECT TOP 1 LastName FROM (SELECT 'Johnson' AS LastName UNION SELECT 'Hudson' AS LastName UNION SELECT 'Jackson' AS LastName UNION SELECT 'Ranallo' AS LastName UNION SELECT 'Curry' AS LastName) AS Last_Names ORDER BY NEWID()) insert into #table values (@a, @b) set @counter = @counter + 1 end select * from #table

Le problème est votre jointure. Voici comment vous pouvez le faire: 

 SELECT FirstName, LastName FROM (SELECT TOP 1 FirstName FROM (SELECT 'John' AS FirstName UNION SELECT 'Tim' AS FirstName UNION SELECT 'Laura' AS FirstName UNION SELECT 'Jeff' AS FirstName UNION SELECT 'Sara' AS FirstName) AS First_Names ORDER BY NEWID())n1 CROSS JOIN (SELECT TOP 1 LastName FROM (SELECT 'Johnson' AS LastName UNION SELECT 'Hudson' AS LastName UNION SELECT 'Jackson' AS LastName UNION SELECT 'Ranallo' AS LastName UNION SELECT 'Curry' AS LastName) AS Last_Names ORDER BY NEWID())n2

Si vous avez besoin de plus d'une combinaison, voici une alternative à l'utilisation d'une boucle pour ce type de chose. 

 declare @max int = 5; with FirstNames(FName) as ( SELECT 'John' UNION ALL SELECT 'Tim' UNION ALL SELECT 'Laura' UNION ALL SELECT 'Jeff' UNION ALL SELECT 'Sara' ) , LastNames(LName) as ( SELECT 'Johnson' UNION ALL SELECT 'Hudson' UNION ALL SELECT 'Jackson' UNION ALL SELECT 'Ranallo' UNION ALL SELECT 'Curry' ) , SortedNames(FName, LName, RowNum) as ( select FName , LName , ROW_NUMBER() over (Order by newid()) from FirstNames cross join LastNames ) select FName , LName from SortedNames where RowNum <= @max order by NEWID();

Si vous voulez générer efficacement plusieurs lignes (vous avez mentionné avoir besoin de générer beaucoup de données), voici ce que je viens de publier plus tôt aujourd'hui sur une autre question sur SO ( Msg 6522, Niveau 16 d'avertissement lors de l'exécution de la procédure stockée clr ). Cette question portait sur la randomisation de 4 champs au lieu de deux seulement, mais je garde ces champs supplémentaires ici pour que vous puissiez voir à quel point il est facile de s'adapter à d'autres scénarios que vous pourriez avoir. Il est plutôt simple de supprimer l'un des champs, et il est également facile d'append de nouvelles valeurs à l'une des 4 variables de table (pour augmenter le nombre de combinaisons possibles) car la requête ajuste dynamicment la plage de randomisation chaque variable de table (c'est-à-dire les lignes 1 – n). 

 DECLARE @TelNumber TABLE (TelNumberID INT NOT NULL IDENTITY(1, 1), Num VARCHAR(30) NOT NULL); INSERT INTO @TelNumber (Num) VALUES ('1525407'), ('5423986'), ('1245398'), ('32657891'), ('123658974'), ('7896534'), ('12354698'); DECLARE @FirstName TABLE (FirstNameID INT NOT NULL IDENTITY(1, 1), Name NVARCHAR(30) NOT NULL); INSERT INTO @FirstName (Name) VALUES ('Babak'), ('Carolin'), ('Martin'), ('Marie'), ('Susane'), ('Michail'), ('Ramona'), ('Ulf'), ('Dirk'), ('Sebastian'); DECLARE @LastName TABLE (LastNameID INT NOT NULL IDENTITY(1, 1), Name NVARCHAR(30) NOT NULL); INSERT INTO @LastName (Name) VALUES ('Bastan'), ('Krause'), ('Rosner'), ('Gartenmeister'), ('Rentsch'), ('Benn'), ('Kycik'), ('Leuoth'), ('Kamkar'), ('Kolaee'); DECLARE @Address TABLE (AddressID INT NOT NULL IDENTITY(1, 1), Addr NVARCHAR(100) NOT NULL); INSERT INTO @Address (Addr) VALUES ('Deutschlan Chemnitz Sonnenstraße 59'), (''), ('Deutschland Chemnitz Arthur-Strobel straße 124'), ('Deutschland Chemnitz Brückenstraße 3'), ('Iran Shiraz Chamran Blvd, Niayesh straße Nr.155'), (''), ('Deutschland Berlin Charlotenburg Pudbulesky Alleee 52'), ('United State of America Washington DC. Farbod Alle'), (''); DECLARE @RowsToInsert INT = 10000; ;WITH rowcounts AS ( SELECT (SELECT COUNT(*) FROM @TelNumber) AS [TelNumberRows], (SELECT COUNT(*) FROM @FirstName) AS [FirstNameRows], (SELECT COUNT(*) FROM @LastName) AS [LastNameRows], (SELECT COUNT(*) FROM @Address) AS [AddressRows] ), nums AS ( SELECT TOP (@RowsToInsert) (CRYPT_GEN_RANDOM(1) % rc.TelNumberRows) + 1 AS [RandomTelNumberID], (CRYPT_GEN_RANDOM(1) % rc.FirstNameRows) + 1 AS [RandomFirstNameID], (CRYPT_GEN_RANDOM(1) % rc.LastNameRows) + 1 AS [RandomLastNameID], (CRYPT_GEN_RANDOM(1) % rc.AddressRows) + 1 AS [RandomAddressID] FROM rowcounts rc CROSS JOIN msdb.sys.all_columns sac1 CROSS JOIN msdb.sys.all_columns sac2 ) -- INSERT dbo.Unsprstb(Firstname, Lastname, Tel, Address) SELECT fn.Name, ln.Name, tn.Num, ad.Addr FROM @FirstName fn FULL JOIN nums ON nums.RandomFirstNameID = fn.FirstNameID FULL JOIN @LastName ln ON ln.LastNameID = nums.RandomLastNameID FULL JOIN @TelNumber tn ON tn.TelNumberID = nums.RandomTelNumberID FULL JOIN @Address ad ON ad.AddressID = nums.RandomAddressID;

Remarques:

  • Les FULL JOIN sont nécessaires à la place de INNER JOIN s pour get le @RowsToInsert total de lignes @RowsToInsert .
  • Les lignes dupliquées sont possibles en raison de la nature même de cette randomisation ET ne pas les filterr en utilisant DISTINCT . Toutefois, DISTINCT ne peut pas être utilisé avec les exemples de données donnés dans la question, car le nombre d'éléments dans chaque variable tableau / table ne fournit que 6300 combinaisons uniques et le nombre de lignes à générer est de 10 000. Si plus de valeurs sont ajoutées aux variables de table de sorte que le total des combinaisons uniques possibles dépasse le nombre de lignes demandé, alors le DISTINCT key DISTINCT peut être ajouté au nums CTE, ou la requête peut être restructurée pour simplement CROSS JOIN tout le variable de table, inclure un ROW_COUNT() , et saisir le TOP(n) utilisant ORDER BY NEWID() (mais cette approche a ses avantages et ses inconvénients).
  • L' INSERT est mis en commentaire afin qu'il soit plus facile de voir que la requête ci-dessus produit le résultat souhaité. Il suffit de décommenter l' INSERT pour que la requête effectue l'opération DML réelle.