Insert - 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

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-27 : 09:00:01

Hello,

I am using a NumberTable function (The one from SQLTeam forums) to create a @Numbers table with 1000 records.

Then I create my tables dummy data as follows:

insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + cast(n as nvarchar)
from @Numbers
where n <= @categories

@categories is the number of categories I want to create.
I create articles table the same way.

Now I want to associate each article to X categories.
For each article X should be a random number between @Min and @Max.

Of course I will set @Max smaller than the number or articles.

I am also using RandomInteger from SQLTeam foruns:

create function dbo.RandomInteger
(
@start int,
@end int,
@newid uniqueidentifier
)

My question is how to do the inserting?
What I have in this moment is the following:

insert into dbo.ArticlesCategories(ArticleID, CategoryID)
select ArticleID, CategoryID
from dbo.Articles
join dbo.Categories on CategoryID in (
?????????????????????????????????????
from dbo.Categories c
order by newid()
)

Thank You,
Miguel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 11:44:10

once you ontain x from random function, use it like this:-

insert into dbo.ArticlesCategories(ArticleID, CategoryID)
select a.ArticleID, b.CategoryID
from dbo.Articles a
cross join (select top (X) CategoryID
from dbo.Categories c
order by newid()
)b

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-27 : 13:59:04

It worked fine.

Thank You,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources