Need To Generate Test (Dummy) Data In SQLServer 7.0
Nov 1, 1999Hi.
Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?
TIA,
Jeff
ADVERTISEMENT
Test - Populating Tables With Dummy Data
Aug 24, 2006In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?
What is 2005's answer to this?
Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where Value = 1
Aug 30, 2007Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.
The function of this, I believe was to make the select work but not retrieve any actual values.
I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.
What I am thinking may not even apply to what I need to do here.
If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.
Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table
but I was hoping this would work.
Example:
Insert Into table
Select
1 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'CA'
Union
Select
2 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'NJ'
Union
/*Final Select */
Select
6 as RecId,
' ' as field1,
field2
From test1
Where 'some value' = 1'
Thanks much for your assistance!!!
TADEG
SQL Server 2008 :: How To Generate Test Data Using Only VS 2013
Sep 29, 2015I've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.
View 5 Replies View RelatedAccomodating 'Dummy' Data?
Jul 23, 2005Hello,I am working with a vb6 Windows application that runs on Tablet PC's.There are about five SQL Server 2000 databases that tie into theapplication. SQL Server MSDE runs on the tablets, and currently thereis only one instance running. There are efforts underway to upgrade theapplication to a .NET SmartClient.One enhancement that needs to be made to the current application willbe to provide a 'training mode' in the application for the users. Theuser will use the same application, but will work with 'dummy' datainstead of live data when in training mode.So far as I can tell, the easiest way to accomodate this would be tohave a second instance of MSDE with 'copies' of all the databasesrunning, but with the 'dummy' data contained in the databases copies.The application would have some mechanism for switching to 'trainingmode', and maybe by some change of a connection string, the dummy datawould be presented in the application.So aside from accomodating a 'training mode' feature in theapplication, the application would remain the same, and the databaseschema for the databases would remain the same. Only the data wouldchange for 'training mode'.I'm looking for some ideas on how to approach this. This application isused by five thousand field agents, and so we can't support thisimplementation on a case by case basis. So my thought is that thesolution needs to have 'few moving parts'. If we can do this and getaway from having two instances of MSDE, that would be great. I have nottried to set up identical databases in different directories on thesame instance (I figure that would cause a problem in a system table ortwo someplace, but maybe not), but its one thing I want to investigate.If you have any ideas on this, please share!Thank you for your help!CSDunn
View 1 Replies View RelatedInserting Dummy Data
Aug 27, 2006 No one answered this question, how do people normally insert dummy data into tables? I mean, where does the data typically come from? what process is used to insert the data? Note: This is for testing purposes, eventually to be tested in a Web-based front-end which I know nothing about.
thx,
Kat
Create Dummy Data
Feb 24, 2008Hello,
I have 4 tables: Folders, Files, Tags and FilesTags.
I need to insert some dummy data for testing as follows:
1. Insert 100 records in Tags tables.
Give Tag 01, Tag 02, Tag 03, ... to Tag names.
For this I have the following:
declare @i integer
select @i = 1
while @i <= 100
begin
insert into Tags ([text])
select 'Tag ' + right('000' + convert(varchar(3), @i), 3)
select @i = @i + 1
end
What I am missing is the following:
2. Insert 10 records in Folders tables.
Each folder should be filled as follows:
FolderID = New Guid
Name = "Name" + N (1, 2, 3, ...)
CreatedDate = Current Date
3. For each Folder I want to insert a random number of Files.
It can be added 0 to 10 Files.
Each file should be filled as follows:
FileID = New Guid
FolderID = Folder ID
Description = "Description" + Folder Number + File Number
4. For each file added associate it with a random number of Tags.
It can be 0 to 10 Tags and it should be done as follows:
Insert record in FilesTags where:
TagID = Random TagID taken from table Tags
That has still not be used in current File
FileID = Current FileID
Could someone, please, help me doing this?
My tables are as follows:
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
CreatedDate datetime not null,
[Name] nvarchar(200) null
)
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
Description nvarchar(2000) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
)
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
Thank You,
Miguel
Auto Generate ID'S In Sqlserver
May 27, 2008Hi,My requirement is i want to auto generate ID'S in sqlserver that meansin database one column is there recordid for that coloumn i want to add numbers with no duplication like JDS 10001,JDS 10002,JDS 10003............like that if any record added in database means automatically add that type of id'sif suddenly we deleted that record means that id will not add again how to do dat??
View 5 Replies View RelatedUnable To Generate Commandtexts For SQLServer Compact Edition 2005
Nov 28, 2007Hello,
I tried to connect Northwind.sdf from VB.NET 2005, though connection went right, I am getting an error like the following
Some updating commands could not be generated automatically. The database returned the following error :
Failed to find or load the registered .Net Framework Data Provider
I checked the machine.config file and found the following entries under the DbProviderFactories
Code Block
<DbProviderFactories>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Compact Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
<add name="SQL Server Compact Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Compact Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
Please help, thanks in advance.
Sampling Data Set Via Integration Services Data Flow For Data Mining Models Without Saving Training And Test Data Set?
Nov 24, 2006Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express
Sep 20, 2006Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
DB Engine :: Replicate A Master Test Database To 100 Test Environments?
Oct 12, 2015We are setting up a test lab environment with 100 machines. Â We want one master testing db that gets replicated to each to run scripted application tests nightly. Â
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. Â I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Unit Testing For SSIS - To Test Or Not To Test?
Oct 17, 2006Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
How To Test SSis Package And What Are The Things I Need To Test It ?
Nov 27, 2007
hi every one,
i need to test SSIS pacakge which will import data from different database where record count is around 5 millions.
iam planning to test it through c# code as well as manually also.
SSIS source : consist of 7 tables
SSIS destination :consist of 7 tables
Using c# code iam trying to run ssis package through batch file.
i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept.
am i going right way ,can any one suggest best and productive way to test the ssis package .
what are the other things i need to test it.
do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination.
Regards
Arif shareef
Test Image Data
Feb 28, 2007Hi I have the following table and just wondered if theres an easy way of inserting test data with the image field not null?
Table: file
Fields: file_id(int), filename(varchar), data(image).
Any help would be great!
Cheers, Mark
SQL Dummy!
Jul 21, 2007Thank you to the two persons who took time to respond to my question.
I have to tell you - I know absolutely NOTHING about SQL.
Can you please tell me in language a two-year-old could understand, "What is SQL?"
Inserting Test Data In To The Database
Jan 3, 2001How can i insert test Data in to the Database,I want to insert one million records in to the table,This is to test Database Performance.
Can anyone help me in this regard,Do we have any scripts for this purpose???
thanks
Mar
Generating Test Data In SQL Server
Jul 3, 2006Hi,
I use SQL Server 2005 Dev Edition and am not new to making databases (then again, I've had enough experience and my dad does the same thing).
I am (unfortunately) a university student and for my dissertation I am going to produce a SQL Server database with a strong emphasis on data mining.
Obviously, for the data mining to be useful at all I need to produce loads and loads of test data.
Fair enough, and there are applications which do this, such as EMS Data Gen, but can anyone recommend me any other data gen utilities? EMS Data Gen has poor handling of unique attributes, and as I am doing a car manufacturer this will give me problems when I come to the registration number attribute.
Also, why are utilities for SQL Server (and Oracle at that) so expensive? This makes it out of my reach and makes it difficult to build a truly good database that will net me good marks, and demotivates me. :(
Lastly, please feel free to recommend to me any utilities for SQL Server - such as performance monitors, backup utilities. Anything. But if they are priced utilities, they have to be sensibly priced (<£100), because I cannot yet afford to pay >£1k on such utiltiies.
Thanks
Train And Test Data Sets
Feb 8, 2007I've seen that sometimes is better to split the table into a test dataset and a training dataset, and I'll appreciate if anyone can explain why is this...
thanks
Santiago Aceñolaza
Argentina
Way To Efficiently Make Test Data ?
Dec 26, 2006We have a website that accesses our SQL databases. In the past, we used our internal employees to improve our SQL databases. However, we want to outsource the work.
There is a lot of information we would like to keep private from the outsourcing.
Is there a way to efficiently make test data throughout our database without changing our original database?
Is a way to easily update our database to the new changes?
I found this product through Google€¦ EMS Data Generator for SQL Server
http://www.sqlmanager.net/en/products/mssql/datagenerator
Would this program help us make test data?
Thanks in advance
-Devin
Test Connection Successful, Yet No Data
May 18, 2007I run XP Professional on a home machine. I recently installed VB 2003 with MSDE. When I go to the server explorer window, right click to add a connection, the Data Link properties dialog box pops up as it should. I select the name of home server, indicate integrated security, and select a sample database such as model or master from the list. The test connection is successful and clicking OK causes the connection to appear in the Server Explorer window. Yet when I go to open the connection, there is no data. The folders for Tables, Stored Procedures and so forth appear but they are empty. I have another 2000 database that a friend sent me. If I try to indicate in the Data Link propetries dialog box that I would like to attach this database, I get the same symptom. Test connection successful, yet when I create and open this connection, the folders are empty. I am a newbie to ADO .NET and I am not sure where to begin. Can someone help?
View 3 Replies View RelatedDummy Question
Mar 3, 2004Hello,
For sure that this is a dummy question but can you explain me how SQL Server reacts to the fact that we have the same application running in two different pc's and this two applications intent to insert,update, delete or even read a record from the same table
Does "he" blocks the record until one of them leave the table ??? And if the other tries to update at the same time ???
Thanks,
Paulo
Dummy Table?
Feb 17, 2004Hi!
I'm new in SQL SERVER 2000 and i'm looking for some kind of predefined dummy table. You know something like the "dual"-Table in Oracle. I want to use it for queries like that:
Select 1, 'auto' from dummy_table
Does anyone know, if something like that in SQL SERVER 2000 exists?
Nick
Dummy Question
Jun 6, 2006It seems every month when I'm diluged by lots of data I fall back on olereliable, Excel. But, I get over 50,000 rows of data to scrub. A colleagueof mine suggested I use a database. Seems simple so far, but having dabbledin Access, it has always not so intuitive to understand. My question istwo-fold, is SQL a database. I believe its the language of some otherdatabase. If I'm corrent on the later, what database(s) use SQL?Tony
View 1 Replies View RelatedMove A Subset Of Data From Production To Test
Nov 18, 2005Here is my requirement.
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
Restore Data From Live System To Test
Jun 16, 2015I have two SQL Databases on separate servers, live and test. I have been asked to copy the data from the live system and put it into test. They are SQL Management Studio 2008 running on MS Server 2008R2.
Could a simple backup of the database, then copy that file to the test system and restore the database from that point work or it there more to it?
CLR Test Script SELECT Returns No Row Data
Sep 12, 2006Hi,
The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"
I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)
But not the actual row is displayed whereas I would expect to see something like:
Name Address
---- -------
John Doe
No rows affected.
(1 row(s) returned)
I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.
Why no results in first case?
Thanks,
Bahadir
Transact SQL :: Create Test Data Using Script For Each Row
Apr 22, 2015I am looking for a sql code snippet which read data from below table
UserId username contact
 1     Anil   111
 2     Sunil  222
and insert data to below table with some test data appending sequence number 1,2,3 for only City and Email. Both are different tables and does not have any referencial integrity.No of records inserted for user is configurable for example count = 3
Username City Email
Anil     city1 email1
Anil     city2 email2
Anil     city3 email3
Sunil     city1 email1
Sunil     city2 email2
Sunil     city3 email3
Create 'Dummy' Rows
Dec 6, 2005Guys - scenario/DDL/DML below
Create table #Periods (Period INT , Frequency INT , startdate datetime NULL , enddate datetime NULL )
insert #Periods (Period , Frequency , startdate , enddate)
select 0 , 3 , '01-Nov-2004' , '30-Nov-2004'
union all
select 1 , 3 , '01-Dec-2004' , '31-Dec-2004'
union all
select 2 , 3 , '01-Jan-2005' , '31-Jan-2005'
union all
select 2 , 6 , '01-Nov-2004' , '30-Nov-2004'
union all
select 3 , 6 , '01-Dec-2004' , '31-Dec-2004'
union all
select 4 , 6 , '01-Jan-2005' , '31-Jan-2005'
select * from #periods
Period Frequency startdate enddate
03 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
13 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
23 2005-01-01 00:00:00.0002005-01-31 00:00:00.000
26 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
36 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
46 2005-01-01 00:00:00.0002005-01-31 00:00:00.000
For any frequency (in this simple example 6) where the 'lowest' period is not 0, I need to create dummy rows
so here I need to create the following
Period Frequency startdate enddate
06 NULL NULL
16 NULL NULL
I've built a temp table to identify the min period for each Frequency but am not sure where to go from here using this to do the inserts ?
select
min(Period) as MinPeriod ,
ResetFrequency
into #Periods2
from #Periods
group by ResetFrequency
select * from #CashFlow2
MinPeriodResetFrequency
03
16
Dummy Table In SQL Server
Apr 19, 2004Hi, Is there a dummy table in SQL Server just like DUAL in Oracle?
Thanks..
Question About Dummy Constraint
Jul 20, 2005I've written code that dynamically builds an sql query based onvarious constraint possibilities.The problem is the code would have been very complex had I not come upwith a dummy constraint as a kind of place holder in the statement.To avoid complex logic that determines if there was another constraintbefore any other constraint and hence the need to add, say, AND ornot, I came up with a dummy constraint so that every subsequentconstraint will begin with AND. There's no need to determine whetherto add AND or not. This makes the coding much simpler because allthat needs to be done is ask if a certain condition exists then addthe constraint along with AND in front every time.So what I did was create the statement like this:SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraintAND 2nd Constraint and so on.See if the 1 > 0 condition were not there it would be necessary tofirst determine the first actual contraint and not add AND in front ofit and then add the rest with ANDs in front of every one.I should add that the user does not have to select any constraint andthat's the problem. I need to stick that WHERE 1>0 in there so thatthere doesn't need to be a determination of which other, if any,constraints are selected.Even if my explanation above is not well understood, believe me thefront-end coding is much easier this way.My question is does the 1 > 0 conditional check present the databasewith any significant overhead or as far was dummy constraints go isthis as good as any other?-David
View 4 Replies View RelatedQuery To Compare Table Data Between Test And Production?
Jul 23, 2005I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedIs There Anyway To Do Data Mining Model Test Via SSIS Package?
Dec 1, 2006Hi, all here,
I am wondering if there is any kind of ways for us to test data mining models via SSIS package? That'll be quite helpful if there is such a way.
Looking forward to hearing from your guidance and thanks a lot in advance.
With best regards,
Yours sincerely,