Generating entries every month

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)

Generating entries every month

Author

Topic

AndrewStrout
Starting Member

5 Posts

Posted - 2007-12-11 : 05:38:45

Ok, heres my scenario: I have a members table and a subs table.
Every month a new sub entry needs to be made for every member.
I could do this via programming code in c#, but because of the number of members, this is not an option. The SQL server needs to do the work and hence I need to use a query.

The subs table has columns: Id, Member, x, y, z
So the SQL statement must take the member.Id for each member and create a sub entry.

The x,y,z is other data that doesn't come from the Member table. It could possibly be inputted by using a stored procedure. (It would be the same for each entry)

Is this possible?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-11 : 05:47:00

what are the subentries?
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

AndrewStrout
Starting Member

5 Posts

Posted - 2007-12-11 : 06:05:59

Sample Data

Member Table:
Id, Name, Surname
1234, John, Doe
1235, John1,Doe1
1236, John2,Doe2
1237, John3,Doe3

Subs Table:(EMPTY) Id, Member, Amount, DateDue, DatePaid(NULLABLE)

After executing the query with paramaters '100','2/2/2008':

Members Table remains unchanged

Subs Table:
Id, Member, Amount, DateDue, DatePaid
1, 1234 , 100 , 2/2/2008, NULL
2, 1235 , 100 , 2/2/2008, NULL
3, 1236 , 100 , 2/2/2008, NULL
4, 1237 , 100 , 2/2/2008, NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:10:22

INSERT Subs (Member, Amount, DateDue)
SELECT t.Member, 100, '2/2/2008'
FROM MemberTable AS t

E 12°55'05.25"
N 56°04'39.16"

AndrewStrout
Starting Member

5 Posts

Posted - 2007-12-11 : 06:35:42

Peso that query doesn't seem to work.

I changed it to:

INSERT Subs (Member, Amount, DateDue)
SELECT t.Id, '100', '2/2/2008'
FROM Member AS t

Whats the function of the "Member AS t". Would it not be the same as:
INSERT Subs (Member, Amount, DateDue)
SELECT Member.Id, '100', '2/2/2008'
FROM Member
?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:46:58

Yes, the very same.

E 12°55'05.25"
N 56°04'39.16"

AndrewStrout
Starting Member

5 Posts

Posted - 2007-12-11 : 07:02:05

I still cant get the query to work.

Since an Id needs to be generated, I added the Id field.

INSERT Subs (Member, Id, Amount, DateDue)
SELECT Member.Id, newid(), '100', '2/2/2008'
FROM Member

SQL Sever says: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Member'.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-11 : 07:05:04

if ID is an identitiy column in your Subs table you don't need to include it, it will just increment automatically

Em

AndrewStrout
Starting Member

5 Posts

Posted - 2007-12-11 : 08:09:05

Ok, got it to work. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources