|
So if your data was:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000
4 | 2 | 1 | 2000 | 1000
5 | 3 | 1 | 3000 | 1100
6 | 4 | 2 | 5000 | Null
7 | 5 | 4 | 1000 | Null
and you said that if the user select 1 for bankid, then you would like the result to be:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000
Is that correct? Are all the rows and columns present? Since that would be simply:
SELECT *
FROM TableName
WHERE IssuedBankId = 1
I suppose that this isn't what you're looking for?
|
|
|
|
|
Thank U Mika,
U made my job simpler , Though I was not searching for that, with ur previous sln I can proceed further.
------
Kiran
|
|
|
|
|
|
I need to import data from one Access database to another with virtually identitical structurers (the target database may have more columns, but it has every column that the database to be imported has). I want to do this for specific tables using SQL, but my database is protected by a database password. How do I construct the SQL statement to include the password?
Thanks.
|
|
|
|
|
polishprogrammer wrote: How do I construct the SQL statement to include the password
The SQL statement doesn't include the password, only the connection does.
The solution varies depending which tools you're about to use, but basically you take one connection to the access db, read data from there and put it to sql server using another connection. You could do this with c#, SSIS/DTS (depending on the db version), linked server etc.
|
|
|
|
|
Thanks, but both the databases are MS Access databases. I need to import data from one Access database into another Access database. I will be using C# to do this operation. How would I include the connection in any SQL operation I might perform? I've tried to Merge 2 data tables. It works (usually), but takes a VERY long time. That's why I was hoping to find something quicker, such as a SQL statement perform the work. If my databases were not secure, I could do this very easily, but the database password complicates things.
Thanks.
|
|
|
|
|
I guess I would open a connection to the source db and then loop through the data and put the data to the target db using another connection. So I wouldn't use data tables in the middle. Also for removing duplicates I would order the source data.
It's been awhile since I last used access so there may be another ways, but I think the logic I described would get the job done.
|
|
|
|
|
Mika,
I found this statement worked to import the data, but it does not take care of the issue of duplicates. I need to customize this statement a bit to ensure that I'm only selecting those records from the C:\Program Files\Northwind.mdb database, say, that are not already in the target database. Sort of, perhaps, a reverse INNER JOIN. If you have any suggestions on that, I would appreciate it. Thanks.
INSERT INTO Employees
SELECT Employees.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees;
|
|
|
|
|
Aah, they've added external sources to Access. Starts to look like SQL Server.
Anyway, I don't know how you identify a duplicate, but let's say you have a column named ID and based on that you make the decision if it exists or not. In that case you could have for example:
INSERT INTO Employees
SELECT source.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees source
WHERE NOT EXISTS (SELECT 1
FROM Employees target
WHERE target.ID = source.ID)
or even better if EXCEPT is supported by Access, you could have:
INSERT INTO Employees
SELECT *
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees
EXCEPT
SELECT *
FROM Employees
The columns must be in the same order in both tables in all of the statements in order for them to work.
|
|
|
|
|
After some search (and trial and error), I found this statement to work:
INSERT INTO Employees
SELECT e1.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].as e1
LEFT JOIN Employees as e2 ON e1.Field1=e2.Field1 WHERE e2.Field1 IS NULL
The IS NULL comparison selects only those records from the left side of the join that did not already have a match in the right (target) table.
It seems to work well. When I tried to import records through code (DataTable.Merge or some iteration technique), the import process would take many minutes for moderately sized tables (30000 to 70000 records), but using just the SQL it took the application about 6 seconds do the same operation.
The only major change I had to make when implementing this in code was to insert the appropriate variable for the database path and implement a function to get the password, as appropriate, such as Marshal.PtrToStringAuto(logPointer).
Thanks again for your suggestions and I hope this follow up I provided is useful.
|
|
|
|
|
No problem and the follow up was useful! Even though I rarely use Access, it's always good to have understanding how it's developping. Thanks!
|
|
|
|
|
Hi Team,
Needed functionality is upload csv file via bulk insert only.
1)I Created table structure
CREATE TABLE #Test_temp
(
Name varchar(100),
Age varchar(100),
Salary varchar(100),
Dept varchar(100)
)
2)
creating the format file (.fmt)
8.0
4
1 SQLCHAR 0 100 "," 1 NAME SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 AGE SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 SALARY SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 4 DEPT SQL_Latin1_General_CP1_CI_AS
3)
The comma delimited csv data file
Name,Age,Salary,Dept
Scott,21,2343,salary
Tiger,22,34343,acct
It was successfully uploaded but
the input changes is that
In csv file, some filed contains values within (")double quotes with (,) .
like in third row below
Name,Age,Salary,Dept
<code>Scott,21,"23,43",salary</code>
Tiger,22,34343,acct
for this scenario, I took the fmt file as below
1 SQLCHAR 0 100 "," 1 NAME SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 AGE SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "\"," 3 SALARY SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 4 DEPT SQL_Latin1_General_CP1_CI_AS
Could anyone help me out from this scenario.
Tarini Singh
Sr Software Engg.
email-tksingh@zenta.com
|
|
|
|
|
I think you could also set quotation marks on the second field like:
2 SQLCHAR 0 100 ",\"" 2 AGE SQL_Latin1_General_CP1_CI_AS
However, it would be better, if the character data is always in quotation marks. Otherwise you may have problems if the data contains commas etc.
So could you get the data in following format (assuming that the number are actually characters as your table defines them):
"Scott","21","23,43","salary"
"Tiger","22","34343","acct"
|
|
|
|
|
hi everyone
i have create a web page where i place two DropDownList in First i bound Country List and in second one i am bounding places list according to selected country my problem is that when place list have less items it works fine but when list goes large Ex more than 5000 or more it take so much time some times it it show time out error (Sql Server). i also try with Ajax tool kit with Cascading Dropdownlist extender with web service method but problem not solve. here is my code
// for country selection
Public Shared Function getContry() As DataTable
Dim Ds As DataSet
Dim contry_list As String
contry_list = "SELECT Country_List.CountryNo,Country_List.CountryName From Country_List"
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, contry_list) ' "Get_Country_List")
Return Ds.Tables(0)
End Function
/// for Place List
Public Shared Function getplace(ByVal CountryNo As Integer) As DataTable
Dim Ds As DataSet
Dim getplacelist As String
getplacelist = "SELECT Place_List.PlaceNo,Place_List.PlaceName From Place_List " & " WHERE Place_List.CountryNo = " & CountryNo
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, getplacelist)
Return Ds.Tables(0)
End Function
and on .aspx page i am using
Private Sub fillCountry()
Dim dt As DataTable
dt = getContry()
ComboBox1.DataSource = dt
ComboBox1.DataValueField = dt.Columns("CountryNo").ToString
ComboBox1.DataTextField = dt.Columns("CountryName").ToString
ComboBox1.DataBind()
End Sub
Private Sub fillPlace(ByVal CountryNo As Integer)
Dim dt As DataTable
dt = getplace(CountryNo)
ListBox1.DataSource = dt
ListBox1.DataValueField = dt.Columns("PlaceNo").ToString
ListBox1.DataTextField = dt.Columns("PlaceName").ToString
ListBox1.DataBind()
ListBox1.Enabled = True
End Sub
where i am wrong plz help me
|
|
|
|
|
I suspect you need to look at the indices on the tables. An index on Place_List.CountryNo would certainly help. You could also look at caching as the places per country probably do not change much.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
To add to Ashfield's post, try to also cache the countries. That would help the fillCountry sub. As Ashfield wrote, indexing and caching helps the fillPlace sub.
|
|
|
|
|
Have you tried using Same query on Query Analyser. Is there same error or it displayes the result(Although it takes time).
Try checking the timeout of SQLCommand object in function
ExecuteDataset
By default there is no value assigned.
Try assisgning 40000, and check where the error of time out is there.
Alo check the indexing of table.
|
|
|
|
|
thx for support this was error coz thr was no indexing thx a lot
|
|
|
|
|
Hai,
I need a query to list a column in order.it is varchar datatype.column name is accno.I has values like AC1,AC6,AC10,BJ8,BJ20,CK12 etc..
i have to list in order like
AC1
AC2
AC10
BK1
BK2
VH1
please any one help me
|
|
|
|
|
Hi,
maybe you could work with substrings (if all of your values have two leading chars):
<br />
select * from mytable <br />
ORDER BY substr(mycolumn, 0, 2), cast(substr(mycolumn, 3) as int)<br />
Regards
Sebastian
|
|
|
|
|
Unless I missed something, it seems like you just need an ORDER BY clause:
select * from table order by accno
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hmm, I don't think that this is working, because a varchar-column is the order-criteria. So it will end up with this:
AC1
AC10
AC2
since it is ordering it lexicographical.
|
|
|
|
|
Yes, sorry, you are right; substringing will be needed.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hello everyone,
I am learning to install SQL Cluster. I am confused about the following concepts, does anyone have any good ideas?
1. I am always confused about the differences between instance/node concept, and also confused about instance name and virtual server name for a SQL cluster during setup process. Could anyone help to clarify please?
2. Do I need a dedicated sub-network to install a SQL Cluster and connect the dedicated sub-network to the LAN (which SQL clients are working on) by hub/switch/router? Or such dedicated sub-network is not needed and all nodes in the SQL cluster could be in the same network of the clients?
thanks in advance,
George
|
|
|
|
|
George_George wrote: I am always confused about the differences between instance/node concept
Instance is the overall cluster server which is seen by the clients. It represents the SQL Server independently from how many nodes participate in the cluster. So node is the actual installation of the SQL Server. In a cluster yo would have two or more nodes.
George_George wrote: Do I need a dedicated sub-network to install a SQL Cluster and connect the dedicated sub-network to the LAN
I think, not exactly a sub-network, but you need two independent network adapters. For example you cannot share a single network card (even if it has several ports). The idea is that you do not have a single point of failure. A heartbeat protocol is used between the nodes so it's typical to have one network card to serve the clients and one network card solely for the node conversations. This conversation between nodes can be isolated (and typically it's a good idea) from other network traffic.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
