Quickest Way to Fill SQL Table with Dummy Data

How to insert Huge dummy data to Sql server

Why You don't generate those records in SQL Server. Here is a script to generate table with 1000000 rows:

DECLARE @values TABLE (DataValue int, RandValue INT)

;WITH mycte AS
(
SELECT 1 DataValue
UNION all
SELECT DataValue + 1
FROM    mycte   
WHERE   DataValue + 1 <= 1000000
)
INSERT INTO @values(DataValue,RandValue)
SELECT 
        DataValue,
        convert(int, convert (varbinary(4), NEWID(), 1)) AS RandValue
FROM mycte m 
OPTION (MAXRECURSION 0)

SELECT 
        v.DataValue,
        v.RandValue,
        (SELECT TOP 1 [User_ID] FROM tblUsers ORDER BY NEWID())
FROM    @values v

In table @values You will have some random int value(column RandValue) which can be used to generate values for other columns. Also You have example of getting random foreign key.

Insert dummy rows to fill missing values into a SQL Table

You can generate the rows using a recursive CTE:

with cte as (
      select acct, amt,
             dateadd(day, 1, end_date) as begin_date,
             eomonth(dateadd(day, 1, end_date)) as end_date
      from (select t.*,
                   row_number() over (partition by acct order by end_date desc) as seqnum
            from t
           ) t
      where seqnum = 1 and end_date < '2021-06-30'
      union all
      select acct, amt, dateadd(month, 1, begin_date),
             eomonth(dateadd(month, 1, begin_date))
      from cte
      where begin_date < '2021-06-01'
     )
select *
from cte;

You can then use insert to insert these rows into a table. Or use union all if you simply want a result set with all the rows.

Here is a db<>fiddle.

Fastest method to fill a database table with 10 Million rows

Using SQL to load a lot of data into a database will usually result in poor performance. In order to do things quickly, you need to go around the SQL engine. Most databases (including Firebird I think) have the ability to backup all the data into a text (or maybe XML) file and to restore the entire database from such a dump file. Since the restoration process doesn't need to be transaction aware and the data isn't represented as SQL, it is usually very quick.

I would write a script that generates a dump file by hand, and then use the database's restore utility to load the data.

After a bit of searching I found FBExport, that seems to be able to do exactly that - you'll just need to generate a CSV file and then use the FBExport tool to import that data into your database.

Create dummy rows to fill missing values into a SQL Server table

According to your data, 1/C ends a year before the date you have in mind. So, it should also generate rows.

I see the difference from your previous question:

• You have acct/type together.
• The cutoff date is dynamic based on the current date.

The changes are really just tweaks on the earlier query:

with cte as (
      select acct, type, amt,
             dateadd(day, 1, end_date) as begin_date,
             eomonth(dateadd(day, 1, end_date)) as end_date
      from (select t.*,
                   row_number() over (partition by acct, type order by end_date desc) as seqnum
            from t
           ) t
      where seqnum = 1 and end_date < eomonth(getdate(), -2)
      union all
      select acct, type, amt, dateadd(month, 1, begin_date),
             eomonth(dateadd(month, 1, begin_date))
      from cte
      where begin_date < eomonth(getdate(), -2)
     )
select *
from cte;

Here is a db<>fiddle.

---

Related Topics