Member Avatar for manhthaodn

I have 3 tables: A, B, C.

How to insert 100000 rows into table B, while inserting have data is the random data are taken from Table A and C

Thanks for help!!!!

  • 2 Contributors
  • 3 Replies
  • 571 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

clarified the goal (create 1,000,000 rows in Detail with Art_Name and Color_Name taken randomly from Art and Color) and correctly suggested using a cross-join/expansion approach. A reliable, efficient pattern is: (1) materialize Art and Color with stable row numbers, (2) generate a 1..N tally (numbers) set, (3) map each tally row to random Art/Color using a fast hash (ABS(CHECKSUM(NEWID()))), and (4) insert in batches to avoid huge transactions and log growth.

Example (use in a test/dev environment and adapt for production constraints):

SET NOCOUNT ON;
DECLARE @Total INT = 1000000, @Batch INT = 10000, @Done INT = 0;
DECLARE @A INT, @C INT;
SELECT @A = COUNT(*) FROM Art;
SELECT @C = COUNT(*) FROM Color;
IF @A = 0 OR @C = 0 RAISERROR('Source tables empty',16,1);

IF OBJECT_ID('tempdb..#Art') IS NOT NULL DROP TABLE #Art;
IF OBJECT_ID('tempdb..#Color') IS NOT NULL DROP TABLE #Color;
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, Id_Art, Art_Name INTO #Art FROM Art;
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, Id_Color, Color_Name INTO #Color FROM Color;

WHILE @Done < @Total
BEGIN
  ;WITH Nums AS (
    SELECT TOP (@Batch) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @Done AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
  )
  INSERT INTO Detail (Id_Art, Id_Color, Art_Name, Color_Name, Detail_Content)
  SELECT a.Id_Art, c.Id_Color, a.Art_Name, c.Color_Name, NULL
  FROM Nums n
  CROSS APPLY (SELECT ((ABS(CHECKSUM(NEWID())) % @A) + 1) AS an) r1
  CROSS APPLY (SELECT ((ABS(CHECKSUM(NEWID())) % @C) + 1) AS cn) r2
  JOIN #Art a ON a.rn = r1.an
  JOIN #Color c ON c.rn = r2.cn;

  SET @Done = @Done + @@ROWCOUNT;
END

Notes and cautions: if Detail.Id_Detail is an IDENTITY, omit it from the INSERT. Large inserts are faster with indexes/constraints disabled and with minimal logging (SIMPLE or BULK_LOGGED recovery and table-level TABLOCK), but those changes affect backups and must not be done casually on production. If truly unique (non-repeating) Art–Color combinations are required, generate the Cartesian product first and sample from it instead of random-mapping. Batch size, locking hints, and whether to drop/rebuild indexes should be chosen according to server resources and recovery requirements.

Recommended Answers

All 3 Replies

Member Avatar for BitBlt

What have you already tried?

Also, you aren't being specific enough to allow any help. Here are some sample questions that might help you refine your request.

Does it have to be truly random? Or can you just cross-join the two source tables and use results from that? Do the source tables A and C have alpha or numeric data? What about the target table B? What columns are of importance for this "random" data?

Does it have to be 1,000,000 rows (like the thread title) or 100,000 rows (like the question)? Or just some large unspecified number? How many rows are in the two source tables?

Are you looking at populating the table for use as test data (as in volume testing or edge-boundary condition testing)? Or is there a specific purpose for generating this data (like production simulation with data masking or obfuscation)?

Sorry I can't be more helpful. Without more detail, I can't provide much guidance.

Member Avatar for manhthaodn

I will send my sample data to get clearly view on the problem that i need help.

I described my problem:

1. I have 3 tables: Art, Color, Detail which Art and color tables have data (Table Art: Id_Art: int, Art_Name: varchar; Table Color: Id_Color: int; Color_Number: numeric; Color_Name: varchar; Table Detail: Id_Detail: int; Id_Art: int, Id_Color: int, Detail_Content: varchar, Art_Name: varchar, Color_Name).
2. The requirement is that I must creat 1,000,000 rows of data in Table Detail. Data in columns: Art_Name, Color_Name taken randomly from two tables respectively (Column Detail_Content: null)

Best Regards!

Member Avatar for BitBlt

Haven't seen your data yet. How many rows in each table? Again, you might just execute a cross-join between Art and Color a bunch of times, until you wind up with a million rows. Of course, you will probably not have completely unique rows (except for Id_Detail) unless you have a sufficiently large number of source rows.

Best of luck!

Edited by BitBlt because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.