Insert Dummy Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Dummy Data

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-24 : 08:56:39

Hello,

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 09:45:06

Try this:-

2.INSERT INTO Folders
 SELECT NEWID(),GETDATE(),'Name'+ CAST(number as varchar(2))
 FROM master..spt_values
 WHERE Type='p' AND number BETWEEN 1 AND 10


3.INSERT INTO Files
  SELECT t.FileID,
         t.FolderID,
         'Description' + CAST(t.FolderID AS varchar(50)) +  CAST(t.FileID AS varchar(50))
  FROM (SELECT NEWID() AS FileID,
        FolderID
        FROM Folders
        CROSS JOIN master..spt_values v
        WHERE v.Type='p' AND v.number BETWEEN 1 AND 10
        ORDER BY NEWID()
        )t
 
4.INSERT INTO FileTags
  SELECT t1.FileID,t2.TagID
  FROM (SELECT FileID FROM Files ORDER BY NEWID())t1
  CROSS JOIN (SELECT TOP 10 TagID FROM Tags ORDER BY NEWID()) t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources