inserting data from one table to another

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

inserting data from one table to another

Author

Topic

mandrews1234
Starting Member

9 Posts

Posted - 2009-07-02 : 14:26:41

I have this sql code
insert into dealers (dealerid, dealershipid, isprimarycontact) values ((
select aspnet_users.userid from aspnet_users, dealerships
where dealershipname like 'Absolute Aeration, LLC'and username like '500940L'), (
select Dealerships.dealershipid from aspnet_users, dealerships
where dealershipname like 'Absolute Aeration, LLC'and username like '500940L'), '1')
As you can see I'm trying to get the userid into dealerid and the dealershipid into the dealershipid, I also need to insert 1 into the isprimarycontact. What is wrong with this syntax? Thanks,

Michael Andrews
Web Developer
www.intellicominc.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-02 : 15:36:48

It's tough from your code to tell exactly what you're after. This may point you in the right direction. If not, tell us what you want to accomplish, what the table structures are and how they link together.

Jim

insert into dealers (dealerid, dealershipid, isprimarycontact)
select users.userid ,ds.dealershipid,'1'
from aspnet_users users
inner join dealerships ds
on
users.userame = ds.username
where
ds.dealershipname = 'Absolute Aeration, LLC'
and users.username = '500940L'

mandrews1234
Starting Member

9 Posts

Posted - 2009-07-02 : 15:59:18

Dealerships has no column named username so that inner join will not work. What happened is our old programmer gave our client access to the site before it was tested properly. They went in and added all of these users and everything didn't get inserted into all of the proper tables. So the dealers table has their dealershipid there userid (which is called dealerid in that table, don't ask me why) and whether or not they are a primary contact 1 or 0. Now these users got inserted into the users table and the dealerships table but not the dealers table. So the only way for me to find their dealershipid is by searching for the dealership name. The only way I can find the correct userid is by searching for their username. So I need to grab the dealerships.dealershipid and the aspnet_users.userid and insert them into that dealers table along with the int 1. The reason I have the where clause is because I have a csv file that the client kept of all of their usernames and dealership names so I am going to output a bunch of sql statements with php to insert the correct data. So to sum all of that up let's say in the csv file we have

dealership name username
dealership1 asdf
dealership2 qwer
dealership3 zxcv

When I output that I would need to insert the dealerships.dealershipid that has the dealership name like 'dealership1' and the aspnet_users.userid that has the username like 'asdf' I repeat the process for each row in the file so I just need to get one working and I can repeat the process.

Michael Andrews
Web Developer
www.intellicominc.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-02 : 17:43:42

So there is a potential 3rd table, say clientCSV with username and dealershipname that will link users to dealearships via username? If so, you'd need to import that table to the server. I don't know how to link it in the front end.
nsert into dealers (dealerid, dealershipid, isprimarycontact)
select users.userid ,ds.dealershipid,'1'
from aspnet_users users
inner join clientCSV csv
on
users.username = csv.username

inner join dealerships ds
on
ds.dealershipname = csv.dealershipname

and you probably won't need the where clause

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources