| Author |
Topic |
|
SandyK
Starting Member
9 Posts |
 Posted - 2006-03-02 : 18:14:08
|
| Hello... I am using SQL Server Express and ASP.net with C# I need to grab a value from a table in database1 and insert it into a table in database2... not quite sure how to do this? Any ideas??? Thanks muchos! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-02 : 23:32:36
|
| If the databases are on the same server, you can reference remote tables using a fully qualified name in the format [database].[owner].[table/view].[column]If they are on different servers, you may want to create a linked server connection between them. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-03 : 01:55:21
|
| egInsert into DB2.owner.table(columns)Select columns from DB1.owner.tableMadhivananFailing to plan is Planning to fail |
|
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2006-03-03 : 01:57:04
|
| Hi,select cols into [newdb].[dbo].[ytnewtbl] from [newdb].[dbo].[ytoldtbl]sp_addlinkedserver -- To add Remote Serversp_addlinkedsrvlogin -- To map/add remote login -- this need to perform at both end local as well remotethen as simple fire the query above with prefixing server name Andy DavisSql Shield Team--------------------------------------------SQL Server Encryption Softwarehttp://www.sql-shield.com |
|
|
|
SandyK
Starting Member
9 Posts |
Posted - 2006-03-03 : 13:34:49
|
| Hi...I am getting this error: Invalid object name 'ASPNETDB.dbo.aspnet_Users'.This is my code: SqlConnection sqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["MeetingSystemConnectionString"].ToString()); String aspSQL = "INSERT INTO MeetingSystem.dbo.Employee(UserName) SELECT UserName FROM ASPNETDB.dbo.aspnet_Users WHERE UserName = '" + newUserName + "' "; SqlCommand aspCommand = new SqlCommand(aspSQL, sqlConnect); sqlConnect.Open();It is not recognising the database i am trying to get data from...?And when i add another connection, i get an error saying a database with the same name already exists...Any ideas? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-03 : 13:39:23
|
| Perform the INSERT in Query Analyzer. Does it work there if you login with the same credentials as your application?Tara Kizeraka tduggan |
|
|
|
SandyK
Starting Member
9 Posts |
Posted - 2006-03-03 : 13:54:15
|
| I still get this error:Invalid object name 'ASPNETDB.dbo.aspnet_Users'Is my SQL string format incorrect? Or do you think I need to specify in the string the actual path of the database?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-03 : 14:02:29
|
| I'm not sure what you mean by actual path. Are both databases on the same server? Does this work in Query Analyzer? SELECT UserName FROM ASPNETDB.dbo.aspnet_UsersIf not, are you sure aspnet_Users is owned by dbo?Tara Kizeraka tduggan |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-03 : 14:22:21
|
| If u r doing in ASP.Net, have 2 different connections for 2 databases. Then using a record set get data from source DB. Assign the value(s) to variablesUsing the other connection (to the other DB) execute the insert statement which is created with the values brought from source DB. |
|
|
|
SandyK
Starting Member
9 Posts |
Posted - 2006-03-03 : 14:51:36
|
| Hi, thanks for the replies.So this way sounds do-able... i have opened the connection to db,and got an sql string that will select the required field,i know how to create a dataset to store the result, but not sure how to assign a variable to the data that has been found.Any ideas? Thanks in advance for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-03 : 16:31:23
|
| You have to get it to work in Query Analyzer before you have any chance of getting it to work in your application.Tara Kizeraka tduggan |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-04 : 20:26:59
|
| SandyK,Did u find a solution ? |
|
|
|
|
select from a database &insert value into a new db