However, DISTINCT cannot be used with the given sample data in the question since the number of elements in each array / table variable provide for only 6300 unique combinations and the requested number of rows to generate is 10,000. Duplicate rows are possible due to the very nature of this randomization AND not filtering them out by using DISTINCT.The FULL JOINs are needed instead of INNER JOINs to get the entire amount of rows.ON tn.TelNumberID = nums.RandomTelNumberID ON nums.RandomFirstNameID = fn.FirstNameID INSERT dbo.Unsprstb(Firstname, Lastname, Tel, Address) (CRYPT_GEN_RANDOM(1) % rc.AddressRows) + 1 AS (CRYPT_GEN_RANDOM(1) % rc.LastNameRows) + 1 AS , (CRYPT_GEN_RANDOM(1) % rc.FirstNameRows) + 1 AS , ('Deutschland Berlin Charlotenburg Pudbulesky Alleee 52'), ('Iran Shiraz Chamran Blvd, Niayesh straße Nr.155'), (''), ('Deutschland Chemnitz Brückenstraße 3'), ('Deutschland Chemnitz Arthur-Strobel straße 124'), INSERT INTO (Addr) VALUES ('Deutschlan Chemnitz Sonnenstraße 59'), (''), ('Gartenmeister'), ('Rentsch'), ('Benn'), ('Kycik'), ('Leuoth'),ĭECLARE TABLE (AddressID INT NOT NULL IDENTITY(1, 1), INSERT INTO (Name) VALUES ('Bastan'), ('Krause'), ('Rosner'), ('Susane'), ('Michail'), ('Ramona'), ('Ulf'), ('Dirk'), ('Sebastian') ĭECLARE TABLE (LastNameID INT NOT NULL IDENTITY(1, 1), INSERT INTO (Name) VALUES ('Babak'), ('Carolin'), ('Martin'), ('Marie'), DECLARE TABLE (TelNumberID INT NOT NULL IDENTITY(1, 1), It is rather simple to remove any of the fields, and it is also easy to add new values to any of the 4 table variables (to increase the number of possible combinations) as the query dynamically adjusts the randomization range to fit whatever data is in each table variable (i.e. That question dealt with randomizing 4 fields instead of just two, but I am keeping those extra fields here so you can see how easy it is to adapt to other scenarios you might have. ( Msg 6522, Level 16 warning during execution of clr stored procedure ). If you are wanting to efficiently generate multiple rows (you did mention needing to generate a lot of sample data), then here is something I just posted earlier today on another question on S.O. I'm sure it's something simple I am overlooking. I want the results to return one row with a first and last name randomly generated so that each row would have a complete name (no NULL values). N1.FirstName IS NOT NULL OR n2.LastName IS NOT NULL UNION SELECT 'Curry' AS LastName) AS Last_Names UNION SELECT 'Sara' AS FirstName) AS First_Names Here is the code (I only included 5 first and last names each for simplicity purposes): SELECT I wanted to create something I could interchange with the various tests I run where I have to manufacture a lot of data. I managed to create a simple query that selects a random first and last name and inserts them into a result table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |