insert records based on results from 2 tables

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

General SQL Server Forums

New to SQL Server Programming

insert records based on results from 2 tables

Author

Topic

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 10:15:04

I have 3 tables. one is a User table, one is a Station table and the last is a UserStation table.

for every user I need to add a default station in the userStation table.
I am trying to piece the querry together and think i am getting something like the following

The station will be retrieved by something like...

where stationID in(select stationID from station where stationName = '053')

The update will be something like...
UPDATE UserStation SET UserID = (?not sure?), StationID = (?not sure?)

How can i get all the UserID?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-10 : 10:17:20

Can you provide some sample data and expected output.

znwpta
Starting Member

12 Posts

Posted - 2009-07-10 : 10:23:55

I believe you can do something like this

INSERT INTO TABLE1 ([Field1], [Field2]) SELECT [TB2FIELDX], [TB2FIELDY] FROM TABLE 2 WHERE TABLE2ID=X

I never tried with 2 tables, but I think if you tweak it it should work.

Hope it helps.

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 10:24:29

user1 UserID = 670, then other user info.
User2 userID = 540,
Station would have StationID = 1 (autoincrement) and StationName = '053'
then UserStation(row1) would have UserStationID =1 (autoincrement), UserID = 670, StationId = '1'
then UserStation(row2) would have UserStationID =2, UserID = 540, StationID = '1
so on and on...

I need this done for every user in the user table and i won't know the stationID in advance only the StationName of '053' in advance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-10 : 10:28:41

How are the User table and Station table related? Are there common fields to join these two tableS?

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 10:34:27

Only through the join table called Userstation. UserStation looks like this...
UserStationID | UserID | StationID
1 | 670 | 1
2 | 540 | 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-10 : 10:43:12

I'm not sure I'm understanding this....

If the Userstation table already contains the data for UserID and StationID, what should we be updating in that table?

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 10:53:32

this is a new requirement to add a default station value to every user. So for every existing user I need to add a default stationID. The default stationName is '053'
in code, I would get the default StationID by select * from Station where StationName = '053'. assign the reslut to a variable, then I would get all users and loop over all users to create a new UserStation row that would contain the new UserStationID, userID, StationID. but i dont know how to do that as a sql script.

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 11:07:10

OPPS just realized i might have caused confusion in my first post by saying i would do an UPDATE.
I meant I would INSERT a new record into UserStation.
Sorry for any confusion.

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 11:10:21

I am thinking this might work...

DECLARE @StationID int

SELECT @StationID = StationID FROM Station WHERE StationNumber ='053'
DECLARE @UserID int

DECLARE curUser CURSOR LOCAL FOR
SELECT UserID
FROM User

OPEN curUser

FETCH curUser INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO UserStation (UserID, StationID)
values (@UserID, @StationID)

FETCH curUser INTO @UserID

END

CLOSE curUser
DEALLOCATE curUser

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 11:27:27

Yes, that worked. Thanks all for helping me work out the answer.

lance1524
Starting Member

11 Posts

Posted - 2009-07-10 : 12:44:43

Just incase anyone else needs to do something like this, here is my final result...

DECLARE @StationID int
--if the default 053 is not in the station table then add it
IF(NOT EXISTS(SELECT StationID FROM Station WHERE StationNumber = '053'))
INSERT INTO Station (StationNumber)
VALUES ('053')
-- Get the default stationID
SELECT @StationID = StationID FROM Station WHERE StationNumber ='053'

DECLARE @UserID int
--loop over all Users
DECLARE curUser CURSOR LOCAL FOR
SELECT UserID
FROM User

OPEN curUser
--get first user
FETCH curUser INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN

--if the user already has the 053 StationNumber get the next user
IF(EXISTS(SELECT * FROM User s
JOIN UserStation u on u.UserID = @UserID
WHERE u.StationID = @StationID))

FETCH curUser INTO @UserID

--insert the default value into the UserStation table
INSERT INTO UserStation (UserID, StationID)
VALUES (@UserID, @StationID)

--get next user
FETCH curUser INTO @UserID

END

CLOSE curUser
DEALLOCATE curUser

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources