May 30, 2012 at 8:16 am
Hi,
I have a problem, need your help.
the scenario: I have to auto populating a table (named, Performance_Rating_Validity) with store procedure, but a table is the many to many to many relationship, which has 2 column as the primary key: date and 1 column reference from other table (named, Performance). other column in the table(named, Rating) is from the other side of table.
The population should be 100000, but my population in table A is 1000 and table C is 10. But for the combination between table A and C doesn't matter because I can generate 100000 different date .
The problem, I don't know how to get the data from table A and C that will loop until 100000.
Before, I have a code like this with the performance has 100000 population and I could not see how the many to many relationship implemented in that table.
create procedure POPULATE_PERFORMANCE_RATING_VALIDITY(@num int)
as
begin
delete from PERFORMANCE_RATING_VALIDITY -- empty the table if there are any record
declare @counter int -- record counter
set @counter=1
while @counter<=@num
begin
insert into PERFORMANCE_RATING_VALIDITY values ('P'+cast(@counter as varchar(10)) --this part should be change. any Idea?
,dateadd(d, convert(int,-(@counter)), (CAST (getdate() as DATE))),
end,
case when (@counter%8=0 or @counter%8=3) then '1'
when (@counter%8=1 or @counter%8=2) then '2'
when (@counter%8=4 or @counter%8=7) then '3'
when (@counter%8=5 or @counter%8=6) then '4'
end)
set @counter=@counter + 1
end
end
-- Execute the stored procedure
exec POPULATE_PERFORMANCE_RATING_VALIDITY 100000
go
Thank you.
May 30, 2012 at 8:58 am
HI an welcome to SSC. I could help you with this but it is very unclear what you are trying to do. It looks a number of insert statements? You do NOT need a loop for this. Looping in t-sql in notoriously slow.
You should instead use a tally or numbers table. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
If you need help with coding this you find lots of people willing and able to help if you post ddl, sample data and desired output. Take a look at the first link in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 1:12 pm
Sean Lange (5/30/2012)
HI an welcome to SSC. I could help you with this but it is very unclear what you are trying to do. It looks a number of insert statements? You do NOT need a loop for this. Looping in t-sql in notoriously slow.You should instead use a tally or numbers table. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
If you need help with coding this you find lots of people willing and able to help if you post ddl, sample data and desired output. Take a look at the first link in my signature for best practices on posting questions.
Hi sean, thank you for your reply. Yes, it is about number of insert statement.
This is should the tables:
IF EXISTS (SELECT * FROM SYS.objects WHERE TYPE='U' AND NAME ='PERFORMANCE')
DROP TABLE PERFORMANCE
CREATE TABLE PERFORMANCE (
PERFORMANCE_CODE VARCHAR(10) NOT NULL,
NAME VARCHAR(255) NOT NULL,
LOCATION VARCHAR(255) NOT NULL,
PERFORMANCE_DATE DATE NOT NULL,
CONSTRAINT PK_PERFORMANCE PRIMARY KEY (PERFORMANCE_CODE)
)
IF EXISTS (SELECT * FROM SYS.objects WHERE TYPE='U' AND NAME ='PERFORMANCE_RATING')
DROP TABLE PERFORMANCE_RATING
CREATE TABLE PERFORMANCE_RATING (
RATING INTEGER NOT NULL,
CONSTRAINT PK_PERFORMANCE_RATING PRIMARY KEY (RATING)
)
IF EXISTS (SELECT * FROM SYS.objects WHERE TYPE='U' AND NAME ='PERFORMANCE_RATING_VALIDITY')
DROP TABLE PERFORMANCE_RATING_VALIDITY
CREATE TABLE PERFORMANCE_RATING_VALIDITY (
PERFORMANCE VARCHAR(10) NOT NULL,
RATING_VALIDFROM DATE NOT NULL,
PERFORMANCE_RATING INTEGER NOT NULL,
CONSTRAINT PK_PERFORMANCE_RATING_VALIDITY PRIMARY KEY (PERFORMANCE, RATING_VALIDFROM)
)
GO
ALTER TABLE PERFORMANCE_RATING_VALIDITY
ADD CONSTRAINT FK1_PERFORMANCE_RATING_VALIDITY FOREIGN KEY (PERFORMANCE)
REFERENCES PERFORMANCE (PERFORMANCE_CODE)
GO
ALTER TABLE PERFORMANCE_RATING_VALIDITY
ADD CONSTRAINT FK2_PERFORMANCE_RATING_VALIDITY FOREIGN KEY (PERFORMANCE_RATING)
REFERENCES PERFORMANCE_RATING (RATING)
GO
These is the DDL that I used. Then the population of the Performance and Rating are:
--insert into performance rating
INSERT INTO PERFORMANCE_RATING VALUES (1)
INSERT INTO PERFORMANCE_RATING VALUES (2)
INSERT INTO PERFORMANCE_RATING VALUES (3)
INSERT INTO PERFORMANCE_RATING VALUES (4)
INSERT INTO PERFORMANCE_RATING VALUES (5)
INSERT INTO PERFORMANCE_RATING VALUES (6)
INSERT INTO PERFORMANCE_RATING VALUES (7)
INSERT INTO PERFORMANCE_RATING VALUES (8)
INSERT INTO PERFORMANCE_RATING VALUES (9)
INSERT INTO PERFORMANCE_RATING VALUES (10)
--insert into Performance
if exists (select * from sys.objects where type = 'P' and name = 'Populate_Performance')
drop procedure Populate_Performance
go
create procedure Populate_Performance(@num int)
as
begin
delete from PERFORMANCE -- empty the table if there are any record
declare @counter int -- record counter
set @counter=1
while @counter<=@num
begin
insert into PERFORMANCE values ('P'+cast(@counter as varchar(10)),
'Name '+cast(@counter as varchar(10)),
case when (@counter%9=0 or @counter%9=1) then 'Arnhem Theatre'
when (@counter%9=2 or @counter%9=3) then 'Location A'
when (@counter%9=4 or @counter%9=5) then 'Location B'
when (@counter%9=6 or @counter%9=7) then 'Location C'
when (@counter%9=8 or @counter%9=5) then 'Location D'
end,
dateadd(d, convert(int,-(@counter)), (CAST (getdate() as DATE))))
set @counter=@counter + 1
end
end
go
-- Execute the stored procedure
exec POPULATE_PERFORMANCE 5000
go
--the special requires in Performance table, a date just has 1 performance
the desired output for Performance_Rating_Validity:
PerformanceCode RatingValidFrom PerformanceRating
P1 12-12-2010 8
P1 13-12-2010 5
... 14-12-2012 5
... ... ..
P1 14-01-2011 7
P10 12-12-2011 6
P10 13-12-2011 5
P10 14-12-2011 5
... ... ...
The population should be 100000.
Hm, I am using SQL server 2008. just realized that I post in the wrong location, I am sorry. Looking forward for you help. Thank you.
May 30, 2012 at 1:29 pm
Thanks for the ddl. You really don't need a loop for this insert. Use the tally table from the link I sent you and your entire loop becomes this.
create procedure Populate_Performance(@num int)
as
begin
delete from PERFORMANCE -- empty the table if there are any record
insert into PERFORMANCE
select 'P'+cast(N as varchar(10)),
'Name '+cast(N as varchar(10)),
case when (N % 9 = 0 or N % 9 = 1) then 'Arnhem Theatre'
when (N % 9 = 2 or N % 9 = 3) then 'Location A'
when (N % 9 = 4 or N % 9 = 5) then 'Location B'
when (N % 9 = 6 or N % 9 = 7) then 'Location C'
when (N % 9 = 8 or N % 9 = 5) then 'Location D'
end,
dateadd(d, convert(int,-(N)), (CAST (getdate() as DATE)))
from Tally
where N < = @num
end
Now it seems that you are wanting to do something else but it is totally unclear what you want. I think you are wanting to get some data from PERFORMANCE_RATING_VALIDITY? There is nothing in this table based on the sample data you provided. Also, for the sake of making this work let's keep the row count a bit more feasible, like maybe 10 instead of 10,000.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 2:51 pm
Hi Sean,
Yes thank you for editing my SP in populating Performance table. It was succeed when I run the SP on my SQL Server 2008 but when I tried to populate data using this SP, it gives me error message:
(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure Populate_Performance, Line 7
Invalid object name 'Tally'.
As you said, My problem is to populating data in table Performance_Rating_Validity, which is the table between Performance table and Performance_Rating. My population should show user that all population in that table implemented also the relationship. This is also the reason why I have to make quantity differences between those tables. Let make a sample population for those 3 tables (Performance 5 data, Performance Rating = 2 data, Performance_Rating_Validity = 25 data
PERFORMANCE_CODENAME PERFORMANCE_DATE LOCATION
P1 Name 1 22-05-2010 Location A
P10 Name 10 23-05-2010 Location B
P100 Name 100 24-05-2010 Location C
P1000 Name 1000 25-05-2010 Location D
P1001 Name 1001 26-05-2010 Location A
Rating
1
2
--this is my problem, I don't know how to populate this if I have to look the table Performance first to get data performance_code
PERFORMANCE_CODERATINGVALIDFROMRATING
P1 01-06-2010 2
P10 02-06-2010 2
P100 03-06-2010 1
P1000 04-06-2010 1
P1001 05-06-2010 1
P1002 06-06-2010 1
P1003 07-06-2010 1
P1004 08-06-2010 2
P1005 09-06-2010 2
P1006 10-06-2010 1
P1 11-06-2010 1
P10 12-06-2010 2
P100 13-06-2010 2
P1000 14-06-2010 1
P1001 15-06-2010 1
P1002 16-06-2010 2
P1003 17-06-2010 2
P1004 18-06-2010 1
P1005 19-06-2010 2
P1006 20-06-2010 1
P1 21-06-2010 2
P10 22-06-2010 1
P100 23-06-2010 1
P1000 24-06-2010 1
P1001 01-06-2010 1
Hope you can understand what I need. Btw, thank you for the information about Tally.
May 30, 2012 at 2:59 pm
nu_bie (5/30/2012)
Hi Sean,Yes thank you for editing my SP in populating Performance table. It was succeed when I run the SP on my SQL Server 2008 but when I tried to populate data using this SP, it gives me error message:
(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure Populate_Performance, Line 7
Invalid object name 'Tally'.
Read the article I linked in my first post above. It explains what a tally table is, how to create it, and how to use it.
As you said, My problem is to populating data in table Performance_Rating_Validity, which is the table between Performance table and Performance_Rating. My population should show user that all population in that table implemented also the relationship. This is also the reason why I have to make quantity differences between those tables. Let make a sample population for those 3 tables (Performance 5 data, Performance Rating = 2 data, Performance_Rating_Validity = 25 data
- Performance
PERFORMANCE_CODENAME PERFORMANCE_DATE LOCATION
P1 Name 1 22-05-2010 Location A
P10 Name 10 23-05-2010 Location B
P100 Name 100 24-05-2010 Location C
P1000 Name 1000 25-05-2010 Location D
P1001 Name 1001 26-05-2010 Location A
- Performance_Rating
Rating
1
2
--this is my problem, I don't know how to populate this if I have to look the table Performance first to get data performance_code
- Performance_Rating_Validity
PERFORMANCE_CODERATINGVALIDFROMRATING
P1 01-06-2010 2
P10 02-06-2010 2
P100 03-06-2010 1
P1000 04-06-2010 1
P1001 05-06-2010 1
P1002 06-06-2010 1
P1003 07-06-2010 1
P1004 08-06-2010 2
P1005 09-06-2010 2
P1006 10-06-2010 1
P1 11-06-2010 1
P10 12-06-2010 2
P100 13-06-2010 2
P1000 14-06-2010 1
P1001 15-06-2010 1
P1002 16-06-2010 2
P1003 17-06-2010 2
P1004 18-06-2010 1
P1005 19-06-2010 2
P1006 20-06-2010 1
P1 21-06-2010 2
P10 22-06-2010 1
P100 23-06-2010 1
P1000 24-06-2010 1
P1001 01-06-2010 1
Hope you can understand what I need. Btw, thank you for the information about Tally.
Nope, I don't get at all where this data comes from. There has to be some kind of rules, otherwise it is just random information. Try to explain how you generate all this information from 5 rows in performance and 2 rows in rating. In your desired output you have a lot of information that doesn't exist and no clear explanation where it should come from. There are performance codes that don't exists etc etc etc...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 3:07 pm
I just random the data. This case is all about auto populate table. So, based on table Performance and Performance_Rating, i need to auto populating table Performance_Rating_Validity, which is the Performance_Code column should take from Performance table, the Rating column should take from Performance_Rating table and the PerformanceValidFrom column, can be random. But remember, the combination of Performance_code and PerformanceValidFrom should be unique.
May 30, 2012 at 3:19 pm
nu_bie (5/30/2012)
I just random the data. This case is all about auto populate table. So, based on table Performance and Performance_Rating, i need to auto populating table Performance_Rating_Validity, which is the Performance_Code column should take from Performance table, the Rating column should take from Performance_Rating table and the PerformanceValidFrom column, can be random. But remember, the combination of Performance_code and PerformanceValidFrom should be unique.
Are you trying to generate test code or something? You say you just randomize the data. If random test data is what you seek that is an entirely different animal than populating data based on some rules.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 3:21 pm
Sean Lange (5/30/2012)
nu_bie (5/30/2012)
Hi Sean,Yes thank you for editing my SP in populating Performance table. It was succeed when I run the SP on my SQL Server 2008 but when I tried to populate data using this SP, it gives me error message:
(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure Populate_Performance, Line 7
Invalid object name 'Tally'.
Read the article I linked in my first post above. It explains what a tally table is, how to create it, and how to use it.
As you said, My problem is to populating data in table Performance_Rating_Validity, which is the table between Performance table and Performance_Rating. My population should show user that all population in that table implemented also the relationship. This is also the reason why I have to make quantity differences between those tables. Let make a sample population for those 3 tables (Performance 5 data, Performance Rating = 2 data, Performance_Rating_Validity = 25 data
- Performance
PERFORMANCE_CODENAME PERFORMANCE_DATE LOCATION
P1 Name 1 22-05-2010 Location A
P10 Name 10 23-05-2010 Location B
P100 Name 100 24-05-2010 Location C
P1000 Name 1000 25-05-2010 Location D
P1001 Name 1001 26-05-2010 Location A
- Performance_Rating
Rating
1
2
--this is my problem, I don't know how to populate this if I have to look the table Performance first to get data performance_code
- Performance_Rating_Validity
PERFORMANCE_CODERATINGVALIDFROMRATING
P1 01-06-2010 2
P10 02-06-2010 2
P100 03-06-2010 1
P1000 04-06-2010 1
P1001 05-06-2010 1
P1002 06-06-2010 1
P1003 07-06-2010 1
P1004 08-06-2010 2
P1005 09-06-2010 2
P1006 10-06-2010 1
P1 11-06-2010 1
P10 12-06-2010 2
P100 13-06-2010 2
P1000 14-06-2010 1
P1001 15-06-2010 1
P1002 16-06-2010 2
P1003 17-06-2010 2
P1004 18-06-2010 1
P1005 19-06-2010 2
P1006 20-06-2010 1
P1 21-06-2010 2
P10 22-06-2010 1
P100 23-06-2010 1
P1000 24-06-2010 1
P1001 01-06-2010 1
Hope you can understand what I need. Btw, thank you for the information about Tally.
Nope, I don't get at all where this data comes from. There has to be some kind of rules, otherwise it is just random information. Try to explain how you generate all this information from 5 rows in performance and 2 rows in rating. In your desired output you have a lot of information that doesn't exist and no clear explanation where it should come from. There are performance codes that don't exists etc etc etc...
This is the insert statement for Performance Table:
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1', 'Name 1',20100522, 'Location A')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P10', 'Name 10',20100523, 'Location B')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P100', 'Name 100',20100524, 'Location C')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1000', 'Name 1000',20100525, 'Location D')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1001', 'Name 1001',20100526, 'Location A')
And the insert statement for Performance_Rating:
INSERT INTO PERFORMANCE_RATING VALUES (1)
INSERT INTO PERFORMANCE_RATING VALUES (2)
So, the problem is I need to auto populate the Performance_Rating_Validity table but the number of populating is not 5 data(as much as Performance) or 10 data (as much of cross join between these 2 tables) but more that because the primary key of Performance_Rating_Validity table is Performance_code and Performance Date (u can make random for this column, uniquely.
May 30, 2012 at 3:26 pm
You are going to have to explain this clearly. You just keep randomizing stuff. I am not familiar with your project and your explanation is leaving me scratching my head. I don't at all understand what the dates have to do with this at all. The dates are listed with each performance and then the ratings have a date but they don't make sense. I will help you but I just don't get what you are trying to do here.
If you are looking to generate test data, Jeff Moden has a couple of great articles and there lots of other posts about that topic. Just use the search box above.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2012 at 3:33 pm
nu_bie (5/30/2012)
Sean Lange (5/30/2012)
nu_bie (5/30/2012)
Hi Sean,Yes thank you for editing my SP in populating Performance table. It was succeed when I run the SP on my SQL Server 2008 but when I tried to populate data using this SP, it gives me error message:
(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure Populate_Performance, Line 7
Invalid object name 'Tally'.
Read the article I linked in my first post above. It explains what a tally table is, how to create it, and how to use it.
As you said, My problem is to populating data in table Performance_Rating_Validity, which is the table between Performance table and Performance_Rating. My population should show user that all population in that table implemented also the relationship. This is also the reason why I have to make quantity differences between those tables. Let make a sample population for those 3 tables (Performance 5 data, Performance Rating = 2 data, Performance_Rating_Validity = 25 data
- Performance
PERFORMANCE_CODENAME PERFORMANCE_DATE LOCATION
P1 Name 1 22-05-2010 Location A
P10 Name 10 23-05-2010 Location B
P100 Name 100 24-05-2010 Location C
P1000 Name 1000 25-05-2010 Location D
P1001 Name 1001 26-05-2010 Location A
- Performance_Rating
Rating
1
2
--this is my problem, I don't know how to populate this if I have to look the table Performance first to get data performance_code
- Performance_Rating_Validity
PERFORMANCE_CODERATINGVALIDFROMRATING
P1 01-06-2010 2
P10 02-06-2010 2
P100 03-06-2010 1
P1000 04-06-2010 1
P1001 05-06-2010 1
P1002 06-06-2010 1
P1003 07-06-2010 1
P1004 08-06-2010 2
P1005 09-06-2010 2
P1006 10-06-2010 1
P1 11-06-2010 1
P10 12-06-2010 2
P100 13-06-2010 2
P1000 14-06-2010 1
P1001 15-06-2010 1
P1002 16-06-2010 2
P1003 17-06-2010 2
P1004 18-06-2010 1
P1005 19-06-2010 2
P1006 20-06-2010 1
P1 21-06-2010 2
P10 22-06-2010 1
P100 23-06-2010 1
P1000 24-06-2010 1
P1001 01-06-2010 1
Hope you can understand what I need. Btw, thank you for the information about Tally.
Nope, I don't get at all where this data comes from. There has to be some kind of rules, otherwise it is just random information. Try to explain how you generate all this information from 5 rows in performance and 2 rows in rating. In your desired output you have a lot of information that doesn't exist and no clear explanation where it should come from. There are performance codes that don't exists etc etc etc...
This is the insert statement for Performance Table:
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1', 'Name 1',20100522, 'Location A')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P10', 'Name 10',20100523, 'Location B')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P100', 'Name 100',20100524, 'Location C')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1000', 'Name 1000',20100525, 'Location D')
insert into PERFORMANCE (PERFORMANCE_CODE, NAME, PERFORMANCE_DATE, LOCATION)
values ('P1001', 'Name 1001',20100526, 'Location A')
And the insert statement for Performance_Rating:
INSERT INTO PERFORMANCE_RATING VALUES (1)
INSERT INTO PERFORMANCE_RATING VALUES (2)
So, the problem is I need to auto populate the Performance_Rating_Validity table but the number of populating is not 5 data(as much as Performance) or 10 data (as much of cross join between these 2 tables) but more that because the primary key of Performance_Rating_Validity table is Performance_code and Performance Date (u can make random for this column, uniquely.
I am trying to auto populating a table which is come from many to many relationship. I attached the diagram for this 3 tables.
May 30, 2012 at 3:37 pm
Sean Lange (5/30/2012)
You are going to have to explain this clearly. You just keep randomizing stuff. I am not familiar with your project and your explanation is leaving me scratching my head. I don't at all understand what the dates have to do with this at all. The dates are listed with each performance and then the ratings have a date but they don't make sense. I will help you but I just don't get what you are trying to do here.If you are looking to generate test data, Jeff Moden has a couple of great articles and there lots of other posts about that topic. Just use the search box above.
the date in Performance means when the performance be held, but the date in Performance rating means for recording any changing of Performance Rating.
May 31, 2012 at 7:16 am
OK I will try one last time. You keep saying you want to auto-populate this table. I understand the table and relationship but I don't understand the logic of how to make up the data. If you can explain it in a way that makes sense I will help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 3:27 am
Sean Lange (5/31/2012)
OK I will try one last time. You keep saying you want to auto-populate this table. I understand the table and relationship but I don't understand the logic of how to make up the data. If you can explain it in a way that makes sense I will help you.
Hi Sean,
this problem I've got when I'm doing my assignment. My assignment's intention are to populate performance (with 5000 data) and to populate the Performance_Rating (with 100000 data) based on the performance code and date (as the primary key).
So, the logic of how to make up the data, I just think that I have to auto populate 5000 data of Performance (based on the columns in the table, there is no regulation of where the content of data from) and then I have to make 100000 data of Performance_Rating which the date are randomize(no matter when is the start and end dates), combine with performance_code from Performance table. The problem, I don't know how to populate this table, I just think when I have @counter(as variable to pass the parameter how many row I'll populate) = 100000, I have to loop the performance_code and random date as much as the @counter.
Hopefully, you can understand with this explanation.
Thank you for trying to help me.
June 5, 2012 at 8:11 am
nu_bie (6/5/2012)
Sean Lange (5/31/2012)
OK I will try one last time. You keep saying you want to auto-populate this table. I understand the table and relationship but I don't understand the logic of how to make up the data. If you can explain it in a way that makes sense I will help you.Hi Sean,
this problem I've got when I'm doing my assignment. My assignment's intention are to populate performance (with 5000 data) and to populate the Performance_Rating (with 100000 data) based on the performance code and date (as the primary key).
So, the logic of how to make up the data, I just think that I have to auto populate 5000 data of Performance (based on the columns in the table, there is no regulation of where the content of data from) and then I have to make 100000 data of Performance_Rating which the date are randomize(no matter when is the start and end dates), combine with performance_code from Performance table. The problem, I don't know how to populate this table, I just think when I have @counter(as variable to pass the parameter how many row I'll populate) = 100000, I have to loop the performance_code and random date as much as the @counter.
Hopefully, you can understand with this explanation.
Thank you for trying to help me.
So you just want random data in there?
Read these two article about generating data.
http://www.sqlservercentral.com/articles/Data+Generation/87901/%5B/url%5D
http://www.sqlservercentral.com/articles/Test+Data/88964/%5B/url%5D
I would help further but this is starting to sound like homework and you don't seem to be able to explain what you want in a way that I can understand.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply