Database

FirstPrevNext

Select from one database table where column value equals a column value in a different database moordoom13-Dec-13 5:52 moordoom 13-Dec-13 5:52  I am trying to pull all data from SQL Database A, Table 1 where column name is X, and the value in column X equals the value in Database B, Table 2, column name Y. So I have 2 databases on the same server. One named A, the other named B. I need to get all the data from A, Table1, where the values of Column Name X are equal to the values in in Column Name Y in B, Table2, and insert/write them to a new table in Database B. Any and all help is appreciated. My SQL writing skills are very sub par. If the title, and the subject differ, I apologize. I am just trying to put the thought thru my head as I write. Re: Select from one database table where column value equals a column value in a different database GuyThiebaut13-Dec-13 6:06 GuyThiebaut 13-Dec-13 6:06  This is the general gist of where I think you need to be heading: SQL insert into B.NewTable select a1.* from A.Table1 a1 join B.Table2 b2 on a1.X = b2.Y “That which can be asserted without evidence, can be dismissed without evidence.” ― Christopher Hitchens Re: Select from one database table where column value equals a column value in a different database moordoom13-Dec-13 8:13 moordoom 13-Dec-13 8:13  a1 and b2 are columns? what is X and Y? Re: Select from one database table where column value equals a column value in a different database GuyThiebaut13-Dec-13 8:52 GuyThiebaut 13-Dec-13 8:52  a1 and b2 are aliases for tables. moordoom wrote: what is X and Y? They are the columns you mentioned in your question. I think you need to read a book on sql or get some basic training, since if you cannot make sense of my answer you need to start reading. “That which can be asserted without evidence, can be dismissed without evidence.” ― Christopher Hitchens Re: Select from one database table where column value equals a column value in a different database joginder-banger15-Dec-13 8:05 joginder-banger 15-Dec-13 8:05  you can solve your problem with the help of SQL join(Inner Join).Sql Join on W3school Re: Select from one database table where column value equals a column value in a different database moordoom16-Dec-13 3:35 moordoom 16-Dec-13 3:35  Looking at the W3 school, it shows table to table joins, not database to database joins. Here is what I have so far... (minus the insert, and it works) SELECT * from Table1 join Database2.Table2 on ColumnX = ColumnY X is a Column in Database1.Table1, and Y is a Column in Database2.Table2. This gets me my info when I run the query on Database1. But now I want write these info into Database2.Table3 and delete what was in there before. So would my statement then be...?? DELETE * FROM Database2.Table3 INSERT INTO Database2.Table3 SELECT * from Table1 join Database2.Table2 on ColumnX = ColumnY I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) sunil_david11-Dec-13 23:44 sunil_david 11-Dec-13 23:44  I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) Simon_Whale12-Dec-13 0:07 Simon_Whale 12-Dec-13 0:07  Is there any error message? Is there any sample code that you can show? Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) noblepaulaziz12-Dec-13 2:18 noblepaulaziz 12-Dec-13 2:18  I have rechecked and have realised that problem emanate from crystal report. I have crystal report based report that uses Store Procedure with parameters(that includes date) as its datasource. I realised that when computer is on US culture , the report displays but when I try to display the report in UK culture it raises exception. I used the same Store Procedure for DataGridView in both cultures (ie US and UK) and it worked with same parameters that did not work in crystal report. Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) thatraja12-Dec-13 2:56 thatraja 12-Dec-13 2:56  You have replied to wrong message(also wrong thread). Get a thatraja Code converters | Education Needed No thanks, I am all stocked up. - Luc Pattyn When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) sunil_david17-Dec-13 19:57 sunil_david 17-Dec-13 19:57  Message: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at LanUpload.BntLan_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Re: I am not able to more than 250 records insert into table in sql server 2008 through (Asp.net upload program) Simon_Whale17-Dec-13 22:13 Simon_Whale 17-Dec-13 22:13  Are you able to show the code where the exception occurs? I think its something to do with the number of connections that you create. Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON What Collation is for best for handling date Internationally? noblepaulaziz11-Dec-13 13:35 noblepaulaziz 11-Dec-13 13:35  My app runs perfectly with MSSQL Server in client/server architecture when my computer is in US culture. But I experience problem when I switched my computer to UK culture. My investigation revealed that it is the date fields in the test data which I created earlier in US culture that is creating the problem My MSSQL Server collation is Latin1_General_CS_AI. I want my app to allow for culture transparency at least for US and UK. modified 11-Dec-13 19:52pm. Re: What Collation is for best for handling date Internationally? PIEBALDconsult11-Dec-13 16:22 PIEBALDconsult 11-Dec-13 16:22  It is to be hoped that you are storing dates as DATE or DATETIME, not as strings, so that should not be a problem. If you are storing dates as string, I strongly reccommend you refactor and do it the right way. Re: What Collation is for best for handling date Internationally? noblepaulaziz11-Dec-13 21:55 noblepaulaziz 11-Dec-13 21:55  Thanks for the reply. Yes I am storing data as DATE. Re: What Collation is for best for handling date Internationally? Mycroft Holmes11-Dec-13 23:03 Mycroft Holmes 11-Dec-13 23:03  The at some point your a converting your test data from string to date and falling in a hole. That is where you need to deal with the format. Collation is not going to help you. Never underestimate the power of human stupidity RAH Re: What Collation is for best for handling date Internationally? Corporal Agarn12-Dec-13 0:46 Corporal Agarn 12-Dec-13 0:46  As stated collation is not the problem. Look at SET DATEFORMAT and see if it will help. Hint you can set it for the process. Re: What Collation is for best for handling date Internationally? noblepaulaziz12-Dec-13 3:51 noblepaulaziz 12-Dec-13 3:51  I have rechecked and have realised that problem emanate from crystal report. I have crystal report based report that uses Store Procedure with parameters(that includes date) as its datasource. I realised that when computer is on US culture , the report displays but when I try to display the report in UK culture it raises exception. I used the same Store Procedure for DataGridView in both cultures (ie US and UK) and it worked with same parameters that did not work in crystal report. Re: What Collation is for best for handling date Internationally? Corporal Agarn12-Dec-13 6:02 Corporal Agarn 12-Dec-13 6:02  You might have to ask in a Crystal Reports forum. Re: What Collation is for best for handling date Internationally? noblepaulaziz12-Dec-13 6:47 noblepaulaziz 12-Dec-13 6:47  thanks for suggestion djj55. I appreciate your assistance Re: What Collation is for best for handling date Internationally? Corporal Agarn12-Dec-13 7:20 Corporal Agarn 12-Dec-13 7:20  Sorry I could not be of more help. Re: What Collation is for best for handling date Internationally? thatraja12-Dec-13 6:19 thatraja 12-Dec-13 6:19  noblepaulaziz wrote: but when I try to display the report in UK culture it raises exception. What's that? You most of people don't include that in your question, don't know why? thatraja Code converters | Education Needed No thanks, I am all stocked up. - Luc Pattyn When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute left join and outer join tsunamigang11-Dec-13 1:05 tsunamigang 11-Dec-13 1:05  What is the difference between LEFT JOIN and LEFT OUTER JOIN?? i.e. what is the difference between INNER JOIN and OUTER JOIN?? I was confused with these two joins.. Re: left join and outer join tsunamigang11-Dec-13 1:17 tsunamigang 11-Dec-13 1:17  I got the answer friends. Both are same. Then why the concept of outer join came?? Re: left join and outer join Tim Carmichael11-Dec-13 1:57 Tim Carmichael 11-Dec-13 1:57  When writing a query, I like to be explicit about what is intended to do. If I want only records with a match, they I say 'INNER JOIN'. If I want partial matches, I say 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN' based on how I feel like writing the query, but it is usually 'RIGHT OUTER JOIN'. Tim

Last Visit: 31-Dec-99 18:00     Last Update: 24-Jun-24 16:07 Refresh ᐊ Prev1...227228229230231232233234235236 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.