You Only need Go 1000 after your INSERT, to fill it 1000 times, just like this:
INSERT INTO dbo.Cusomers(Id, FirstName, LastName) VALUES(1, 'Mohamed', 'Mousavi')
GO 1000
It will make a table with 1000 same rows in it.
Another solution is that you can populate the beginning rows of your table with some data, then you fill the next rows of table by repeating the beginning rows over and over, it means you fill your table by itself:
INSERT INTO dbo.Customers
SELECT * FROM dbo.Customers
GO 10
In the case one or more column are identity (meaning they accept unique values, if it's auto incremental), you just don't place it in your query, for instance if Id in dbo.Customer is identity, the query goes like this:
INSERT INTO dbo.Customers
SELECT FirstName, Last Name FROM dbo.Customers
GO 10
Instead Of:
INSERT INTO dbo.Customers
SELECT Id, FirstName, Last Name FROM dbo.Customers
GO 10
Else you'll encounter this Error:
An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Note: This is sort of an arithmetic progression, so it's going to last a little, don't use a big number in front of GO.
If you want to have a table which is filled a little bit more elaborated then you can achieve that the same way this time by executing a simple query and following these steps:
Choose one of your tables which has a remarkable number of rows, say dbo.Customers
Right click on it and select Script Table as > Create To > New Query Editor Window
Name your new table to something else like dbo.CustomersTest, Now you can execute the query to have a new table with similar structure with the dbo.Customers.
Note:Keep in mind that if it has a Identity filed, change it's Identity Specification to No Since you are supposed to fill the new table by the data of the original one repeatedly.
Run the following query, it's going to be run 1000 times, you can change it to more or less but be aware that it might last minuets based on your computer hardware:
INSERT INTO [dbo].[CustomersTest] SELECT * FROM [dbo].[Customers] GO 1000
After a while you have a table with dummy rows in it!
As @SQLMenace mentioned, RedGate Data Generator is a so good tool to fulfill it, it costs $369, you have a 14 days trial chance Although.
The good point is that RedGate identifies foreign keys so you can apply JOIN in your queries.
You have a bunch of options which allow you to decide how every column is supposed to be populated, every column is anticipated semantically so that related data are suggested, for instance if you have a column named 'Department' it isn't filled by weird characters, it's filled by expressions like "Technical", "Web", "Customer", etc. Even you can use regular expression to restrict selected characters.
I populated my tables with over 10,000,000 records which was an awesome simulation.
Late answer but can be useful to other readers of this thread. Beside other solutions, I can recommend importing data from a .csv file using SSMS or custom SQL import scripts, programs. There is a step-by-step tutorial on how to do this, so you might want to check it out: http://solutioncenter.apexsql.com/how-to-generate-randomized-test-data-from-a-csv-file/
Be aware that importing a .csv file using SSMS or custom SQL import scripts, is easier than creating SQL inserts manually, but there are some limitations, as explained in the tutorial:
If there is a need for thousands of rows to be populated, and the .csv file contains few hundred rows of data it is just not enough. The workaround is reimporting the same .csv file over and over until needed. The drawback to this method is that it will insert large blocks of rows with the same data, without randomizing them.
The tutorial also explains how to use a 3rd party SQL data generator called ApexSQL Generate. The tool has an integrated function to generate large amounts of randomized data from the imported .csv formatted file. Application features a fully functional free trial so you can download and try it to see if it works for you.
http://filldb.info/dummy/ works best. It offers complete settings, choice of how many rows to generate, "real" dummy data, all for free.
I've never seen anything more effective or better at this conditions.
You can generate a whole database or just a table with an easy to use GUI. It is also very elaborate in its settings and options, allowing you to generate dummy data with basically no effort. The GUI has no limits in size and is very extensive in data type options.
To use it, navigate to the link and insert a SQL command that defines the tables or use their dummy tables. Then click next and fill out your rows data types and settings for dummy data population. Then click next and generate the data. Wait. Once done, download the database and import it to your own database server.
发布评论
评论（3）
你只需要在 INSERT 之后
Go 1000来填充它 1000 次，就像这样：它将创建一个包含 1000 个相同行的表。
另一个解决方案是您可以用一些数据填充表的开始行，然后通过一遍又一遍地重复开始行来填充表的下一行，这意味着您自己填充表：
在一个或多个列的情况下是身份（意味着它们接受唯一值，如果它是自动增量），您只是不将其放入查询中，例如，如果 dbo.Customer 中的 Id 是身份，则查询如下所示：
而不是：
否则您将遇到此错误：
仅当使用列列表且 IDENTITY_INSERT 为 ON 时，才能指定表“dbo.Customers”中标识列的显式值。注意：
这是一种算术级数，所以它会持续一点，不要在
GO前面使用很大的数字。如果您想要一个填充得更详细的表，那么这次您可以通过执行一个简单的查询并按照以下步骤以相同的方式实现这一点：
选择一个具有大量行的表，说 dbo.Customers
右键单击它并选择
Script Table as >创建到>新的查询编辑器窗口将新表命名为其他名称，例如 dbo.CustomersTest，现在您可以执行查询以创建一个与 dbo.Customers 具有相似结构的新表。
注意：请记住，如果它有身份字段，请将其
身份规范更改为否，因为您应该用原始表的数据重复填充新表。INSERT INTO [dbo].[CustomersTest] SELECT * FROM [dbo].[Customers] GO 1000正如 @SQLMenace 提到的，RedGate Data Generator 是一个很好的工具来实现它，它的价格为 369 美元，但你有 14 天的试用机会。
优点是 RedGate 可以识别外键，因此您可以在查询中应用 JOIN。
您有很多选项可以让您决定如何填充每一列，从语义上预期每一列，以便建议相关数据，例如，如果您有一个名为“部门”的列，则它不会被奇怪的填充字符，它由“技术”、“Web”、“客户”等表达式填充。甚至您可以使用正则表达式来限制所选字符。
我在表中填充了超过 10,000,000 条记录，这是一次很棒的模拟。
You Only need
Go 1000after your INSERT, to fill it 1000 times, just like this:It will make a table with 1000 same rows in it.
Another solution is that you can populate the beginning rows of your table with some data, then you fill the next rows of table by repeating the beginning rows over and over, it means you fill your table by itself:
In the case one or more column are identity (meaning they accept unique values, if it's auto incremental), you just don't place it in your query, for instance if Id in dbo.Customer is identity, the query goes like this:
Instead Of:
Else you'll encounter this Error:
An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.Note:
This is sort of an arithmetic progression, so it's going to last a little, don't use a big number in front of
GO.If you want to have a table which is filled a little bit more elaborated then you can achieve that the same way this time by executing a simple query and following these steps:
Choose one of your tables which has a remarkable number of rows, say dbo.Customers
Right click on it and select
Script Table as > Create To > New Query Editor WindowName your new table to something else like dbo.CustomersTest, Now you can execute the query to have a new table with similar structure with the dbo.Customers.
Note:Keep in mind that if it has a Identity filed, change it's
Identity Specification to NoSince you are supposed to fill the new table by the data of the original one repeatedly.INSERT INTO [dbo].[CustomersTest] SELECT * FROM [dbo].[Customers] GO 1000As @SQLMenace mentioned, RedGate Data Generator is a so good tool to fulfill it, it costs $369, you have a 14 days trial chance Although.
The good point is that RedGate identifies foreign keys so you can apply JOIN in your queries.
You have a bunch of options which allow you to decide how every column is supposed to be populated, every column is anticipated semantically so that related data are suggested, for instance if you have a column named 'Department' it isn't filled by weird characters, it's filled by expressions like "Technical", "Web", "Customer", etc. Even you can use regular expression to restrict selected characters.
I populated my tables with over 10,000,000 records which was an awesome simulation.
答案较晚，但对该线程的其他读者可能有用。
除了其他解决方案之外，我还建议使用 SSMS 或自定义 SQL 导入脚本、程序从 .csv 文件导入数据。有一个关于如何执行此操作的分步教程，因此您可能需要查看一下：http://solutioncenter.apexsql.com/how-to-generate-randomized-test-data-from-a-csv-file/
请注意，使用 SSMS 或自定义 SQL 导入脚本导入 .csv 文件比手动创建 SQL 插入更容易，但存在一些限制，如本教程中所述：
本教程还介绍了如何使用名为 ApexSQLGenerate 的第三方 SQL 数据生成器。该工具具有集成功能，可从导入的 .csv 格式文件生成大量随机数据。该应用程序提供功能齐全的免费试用版，因此您可以下载并试用它，看看它是否适合您。
Late answer but can be useful to other readers of this thread.
Beside other solutions, I can recommend importing data from a .csv file using SSMS or custom SQL import scripts, programs. There is a step-by-step tutorial on how to do this, so you might want to check it out: http://solutioncenter.apexsql.com/how-to-generate-randomized-test-data-from-a-csv-file/
Be aware that importing a .csv file using SSMS or custom SQL import scripts, is easier than creating SQL inserts manually, but there are some limitations, as explained in the tutorial:
The tutorial also explains how to use a 3rd party SQL data generator called ApexSQL Generate. The tool has an integrated function to generate large amounts of randomized data from the imported .csv formatted file. Application features a fully functional free trial so you can download and try it to see if it works for you.
http://filldb.info/dummy/ 效果最好。它提供完整的设置、选择生成多少行、“真实”虚拟数据，所有这些都是免费的。
我从未见过在这种情况下更有效或更好的东西。
您可以使用易于使用的 GUI 生成整个数据库或仅生成一个表。它的设置和选项也非常精细，允许您基本上不费力地生成虚拟数据。 GUI 的大小没有限制，并且数据类型选项非常广泛。
要使用它，请导航到链接并插入定义表或使用其虚拟表的 SQL 命令。然后单击“下一步”并填写行数据类型和虚拟数据填充的设置。
然后点击下一步并生成数据。等待。完成后，下载数据库并将其导入到您自己的数据库服务器。
http://filldb.info/dummy/ works best. It offers complete settings, choice of how many rows to generate, "real" dummy data, all for free.
I've never seen anything more effective or better at this conditions.
You can generate a whole database or just a table with an easy to use GUI. It is also very elaborate in its settings and options, allowing you to generate dummy data with basically no effort. The GUI has no limits in size and is very extensive in data type options.
To use it, navigate to the link and insert a SQL command that defines the tables or use their dummy tables. Then click next and fill out your rows data types and settings for dummy data population.
Then click next and generate the data. Wait. Once done, download the database and import it to your own database server.