|
hi,i'm new here,it's my first time to ask a question..
I'm trying to join 2 tables together using sql query as following:
insert into table1
select * from
table2
but if there's a record in table2 which disobey referential integrity constraints,the query would be terminated,and the rest were failed to insert.
I have also tried to use cursor to fetch record one by one,but it's too trouble to judge every column type.
I'm looking for some efficient solutions..
oh,I'm so sorry for my poor english!
Thanks a lot in advance,
ChongHui.Ren
-- modified at 8:07 Saturday 14th April, 2007
|
|
|
|
|
I am assuming that the problem you have is that some of the records in table2 already exist in table1. So your query would need to be:
insert into table1
select t2.* from
table2 t2
left join table1 t1 on t2.key = t1.key
where t1.key is null
I am assuming you have a column in both tables called key, most likely you call the column something else. The select statement will only give you the records in table2 that are not already in table1
Hope that helps.
Ben
|
|
|
|
|
thanks for your help
select t2.* from
table2 t2
left join table1 t1 on t2.key = t1.key
where t1.key is null
but i exeute select statement above,the query result is always null,why?
|
|
|
|
|
If the query results are always null that would mean that the rows that are in table1 are already in table2.
You can check this by doing this
Select count(*) from table1
select count(*) from table2 t2
join table1 t1 on t2.key = t1.key
The counts should match if everything that is in table1 is in table2
Hope that helps.
Ben
|
|
|
|
|
Imagin that i made a sql database an its application now i want to install my file into target computer how cam i do that .
|
|
|
|
|
hi ...
i wan to export data in sql server 2005 with wizard but take follow error !!!!!
"Only part of a ReadProcessMemory or WriteProcessMemory request was completed (System)"
is any query (in sql server) that i can export data ?
thanks
|
|
|
|
|
Sounds more like a hardware fault than anything wrong with SQL Server. If you google for the error message you'll see that it crops up a lot on hardware driver support forums.
|
|
|
|
|
hi
can i bind a field in comboBox. i filled a datatable in dataset with data from database and tried to bind a column with comboBox but no data appeared. i set the combobox dataSource to the datatable and set the dataMember to the columnName.
isearched for that and i found the solution is do make a dataView. it works fine but i wanna anther solution if u can help me.
anther thing,
i tried to use the select command of the datatable to filter the data and bind the returned array to combobox but its not work. i set the dataSource to the arrayName and the dataMember to columnName like this
dim dr() as dataRow = ds.tables(0).select("where id > " & id)
comDate.dataSource = dr
comDate.DataMember = "BatchDate"
the table has these columns (ID,BatchDate,....) but the dates doesnt appear
|
|
|
|
|
Mdallal84 wrote: dataMember to the columnName.
Isn't it the DisplayMember you need to set to show data ? The DataMember sets the data associated with the current selection, not the one shown.
I also believey you need to call the DataBind() method.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
you can't bind to a datarow collection.
Consider making a dataview, and then setting the datasource to the dataview.
Also, you need to set the .DisplayMember and the .DataMember.
|
|
|
|
|
Hi in sqlserver stored procedure i am doing some calculations...,
See the below procedure:
select a.empid,b.empName,c.Whrs,c.srg,case when srg=0 then @Outsunday else @Insunday end as days,c.Bassalary,
(Bassalary/(days*Whrs)) as onehour,
sum(AppWHours) as TWhrs,Sum(AppOtHours) as TOThrs
from tblMonthHourDetails a,tblemployeemaster b,tblsalarydetails c where
a.empid=b.empid and a.empid=c.empid and
b.projectid=@projectid and datepart(mm,[SDate])=@months and datepart(yy,[SDate])=@Years group by a.empid,b.empName,c.Whrs,c.srg,c.Bassalary
In that above procedure using case condition i am getting one column "days" like...,
That days value i want to use to calculate for onehour column,
if i give there days(highlighted in above procedure) value its telling error msg like invalid column days like...,
but i want to use that value there how to use...,
Plz any one solve my probs.....,
Magi
|
|
|
|
|
Try:
select z.*, (z.Bassalary/(z.days*z.Whrs)) as onehour
from (
select a.empid, b.empName, c.Whrs, c.srg,
case when srg=0 then @Outsunday else @Insunday end as days,
c.Bassalary,
sum(AppWHours) as TWhrs,Sum(AppOtHours) as TOThrs
from tblMonthHourDetails a, tblemployeemaster b, tblsalarydetails c
where a.empid=b.empid
and a.empid=c.empid
and b.projectid=@projectid
and datepart(mm,[SDate])=@months
and datepart(yy,[SDate])=@Years
group by a.empid, b.empName, c.Whrs, c.srg, c.Bassalary
) z
order by z.empidThis moves most of the source calculations into an inline view (the funny select statement within the main from-clause). You can then use the "z.days" value that you have calculated.
Regards
Andy
|
|
|
|
|
I have a table tbUsers and I selected a row now it has three fields. I want value of first field in txtbox1, second field value in textbox2 and third field value in textbox3. where textbox1, textbox2 and textbox3 are the textboxes in my ASP page.
How can I do that.
Thanks,
|
|
|
|
|
1. create a connection object
2. create a recordset object
3. check whether is BOF/EOF
4. if not assign textboxes with value from recordset
Ex. connecting to SQL Server & Fetching data
<%
Dim rs, mycon, query
set rs=server.CreateObject ("adodb.recordset")
set mycon=server.CreateObject("adodb.connection")
mycon.Open "provider=sqloledb.1; uid=sa; pwd=sa; initial catalog=mydb; data source=myserver"
query="SELECT * FROM tbusers WHERE .....
rs.Open query, mycon, adOpenDynamic
if not rs.EOF and not rs.BOF then
<statements for="" assigning="" values="" to="" textboxes="">
end if
rs.Close
%>
Regards
KP
|
|
|
|
|
Hello
I'm looking for an O/R-Mapper which should be compatible with any/most databases through the OleDb-Provider.
All known an testet ORMs are only available for most common databases such as MSSQL, Access, Oracle, SQLite, MySQL, PostgreSQL, ...
But I need an ORM which is compatible with Paradox and other databases. (Jet/OleDb)
It's enough if the ORM only provide features such as SELECT, INSERT, UPDATE, DELETE and may transactions. Because Paradox doesn't support more.
Or can you give me a hint for problem below?
We're are rewriting an application (now in C++ VLC/OWL) in C# (.NET 2.0). The problem we have is that the new application must support MSSQL as well as Paradox. (Paradox and MSSQL support are Must-Haves)
What kind of ORM would you advise for our problem? Or something different?
Thank you
Danny Meier
|
|
|
|
|
NHibernate works as does DevExpress Persistent Objects.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hello again!
I'm rather new to what SQL means and does (many new "stored procedures"-words for me lately).
So, just the other day i ran into a problem. I designed a program which connects to a SQL Server 2000 Database. And it works just fine on my computer. Still, one of my friends wanted to test the application. I gave him the database files and registered them with SQL Server 2000 (attached to server). He also had VC# Express Edition installed (i think this is the cause and an important clue). When running the .exe, while trying to connect to the databases, he receives an error message which says that there is no connection available for SQL SERVER 2005:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server(   ).
Then, i tried the other way around. I gave him the code of my application, made him run it... and... another error: Cannot open database requested in login 'Consulting'. Login fails. Login failed for user 'GABRIELA\gabi'. I understand that there are users... and i can't synchronize them... but, how do i allow my database to be accesed on any computer with SQL Server 2000 installed on it?
the connection string in the program is:
<br />
<connectionStrings><br />
<add name="cristina_atestat.Properties.Settings.Consulting_coConnectionString"<br />
connectionString="Data Source=(local);Initial Catalog=Consulting;Integrated Security=True;Trusted_Connection=yes;"<br />
providerName="System.Data.SqlClient" /><br />
</connectionStrings><br />
<br />
Thanks a lot in advance,
Catalin David.
|
|
|
|
|
So are you (development machine) using sql server 2000 or 2005?
Nathan Lindley
|
|
|
|
|
i am using sql 2000. my friend has sql 2000 installed. the problem is that (local) points to sql 2005. and i think that is because of vc# express edition which connects mainly to sql 2005. but i want sql 2000. sorry for the confusion.
|
|
|
|
|
Okay then yes, the express edition is whats causing the problem. Id assume he probably installed it when installing Visual Studio 2k5. I actually ran into this problem a few months back, and the solution for me was to uninstall visual studio completely, and reinstall and make sure not to install vc express (custom install if i recall correctly). Id assume you were already on this path so i'm not sure how much help im providing but lmk if you still are pondering a decision.
Nathan Lindley
|
|
|
|
|
oki... will try this. again. just got visual studio 2005 (complete, not express) ... so... will install that, i hope. wish me luck, fingers crossed!
thanks a lot!
|
|
|
|
|
good luck, make sure to COMPLETELY uninstall visual studio.
Nathan Lindley
|
|
|
|
|
completely means anything else than start->control panel->add/remove programs-> uninstall visual c# express edition?
|
|
|
|
|
The reason i wrote that was because, i cant for the life of me think of it now tho, I think you have to clear some registry values. Just try the uninstall via the control panel and if it doesn't work check out this blog:
http://blogs.msdn.com/astebner/articles/574618.aspx
When I had your problem this blog was a life saver.
Good luck.
Nathan Lindley
|
|
|
|
|
Why not attach the db to the instance of SQL 2005?
I suspect whether you are using C# Express or Professional will not makes all that much difference.
Learning is not a spectator sport. - D. Blocher
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
