Database

Re: Bulk Insert in Oracle

15-Aug-08 4:30

You're welcome Smile | :)

Mika

Bulk Insert in Oracle

Member 209771914-Aug-08 1:35

Member 2097719

14-Aug-08 1:35

How can I a make a bulk insert in Oracle from a csv file... like the "Bulk insert" statement in SQL server ..pls help me..

Wendelius14-Aug-08 5:40

SQL 2005 Cross-DB Coding Compatibility [modified]

14-Aug-08 5:40

Hi,

SQL*Loader can be used for inserting data from a file to database. It's not a command like in SQL Server but an utility.

Hope this helps,

Mika

MatthysDT13-Aug-08 23:45

MatthysDT

13-Aug-08 23:45

O.k, I'm re-wording my post.
How can I dynamically change the target database of some SQL code like a stored procedure or a function?
In other words...
I have this:
Execute MyDataBase..procAddHistoryEvent(@Blah,etc)
Update MyDataBase..tblWoof set WoofColumn1 = 'bark952' where WoofColumn2 = @Blah
but my solution requires something like this:
Execute @SelectedDB..procAddHistoryEvent(@Blah,etc)
Update @SelectedDB..tblWoof set WoofColumn1 = 'bark952' where WoofColumn2 = @Blah

My old post_________________________________________________________________________________________

Scenario...
Our system runs multiple DB's on a single SQL2005 Server. The DB naming is usually is not a problem since our software can be configured to which DB it should connect for which data. I.e Client1's application could connect to the DB Client1Data.
So when we restore data for debugging and don't want to overwrite an existing DB we simply restore it as (e.g.) Client1DataTest.
But...
Our software uses multiple DB's, so Client1's software also uses DB's for (e.g.) Client1Messages, Client1Mail etc. So for debugging we would load DB's for Client1DataTest, Client1MessagesTest, Client1MailTest etc.
So my question...
All our DB's run stored procedures (SP's) and in quite a few cases one DB will execute a SP on another DB with the following syntax:
Execute Client1Data..procAddHistoryEvent(@Blah,etc)
or simply select, update, delete and insert data on another DB e.g.:
Update Client1Messages..tblWoof set WoofColumn1 = 'bark952' where WoofColumn2 = @Blah
It's clear how the above is kinda hardcoded.
So when we run our system on a test DB say, Client1DataTest, it still wrongly accesses data from and executes code on Client1Messages instead of Client1MessagesTest.
Is there a better way of doing this OTHER than compiling and executing SQL strings all over the place?
Kind Regards

Doggy treat[^]

modified on Thursday, August 14, 2008 9:03 AM

Re: SQL 2005 Cross-DB Coding Compatibility

Wendelius14-Aug-08 5:44

Re: SQL 2005 Cross-DB Coding Compatibility

14-Aug-08 5:44

Hi,

Synonyms are the helpfull for this

Mika

Mycroft Holmes14-Aug-08 15:45

Mycroft Holmes

14-Aug-08 15:45

evilnoodle wrote:
Our system runs multiple DB's on a single SQL2005 Server

I hope this is not the consequence of allowing your users to name the database when installing (question asked a few weeks ago). If Mika's suggestion does not help then you may need to go down the path of dynamic sql.

If the databases are identical (surely) then move the decision to the connection from the client not the procedure. Then you are executing the proc and the proc does no care what database/server it is on.

If the connection information needs to be within the proc I think you are screwed unless synonyms can help.

Never underestimate the power of human stupidity
RAH

get row index

nelsonpaixao13-Aug-08 13:16

nelsonpaixao

13-Aug-08 13:16

Hi,

id f_name l_name
1 dan silva
2 al gore
5 billy smith
6 jack wilder
9 ben douglas
11 chris ball
13 helen hunter
23 ruth south
24 sondra zelda

how can i get the row number, index for bill smith?
i dont want to get =5
i want to get =3

I created a extra column with 1,2,3,4...etc, but i don´t want to use that!!! D'Oh! | :doh:

nelsonpaixao@yahoo.com.br

Mycroft Holmes13-Aug-08 13:48

Mycroft Holmes

13-Aug-08 13:48

To get the record use and use the additional column
Select * from TableName where l_name = 'billy smith'

To get the rowindex in the table - what for! I can think of a number of reasons, most of which are in the UI.
Look into ROWNUMBER in BOL, this may help

[edit] forget rownumber look at RANK or ranking functions[edit]

Never underestimate the power of human stupidity
RAH

PIEBALDconsult13-Aug-08 14:08

PIEBALDconsult

13-Aug-08 14:08

That would depend on ordering as well.

I imagine a function could be written to accomplish it, but doing so after you retrieve the data may be best.

RyanEK13-Aug-08 17:20

RyanEK

13-Aug-08 17:20

Hi,

Why not...

select row_number() over (order by id asc) as Rowindex, *
from [Table]

Ryan

Blue_Boy13-Aug-08 22:08

Blue_Boy

13-Aug-08 22:08

<br />
ALTER TABLE tableName ADD value int NULL<br />
<br />
DECLARE @i AS INT<br />
DECLARE @j AS INT<br />
SET @i=0<br />
SET @j=0<br />
<br />
WHILE @j<=(SELECT MAX(id) FROM tableName )<br />
BEGIN<br />
SET @j=@j+1	<br />
	IF(SELECT COUNT(id) FROM tableName WHERE id=@j)=0<br />
	BEGIN<br />
	SET @i=@i+1	<br />
	UPDATE tableName SET value=@i WHERE id=@j<br />
	END	<br />
END<br />
<br />
SELECT * FROM tableName WHERE value =3<br />
ALTER TABLE tableName DROP COLUMN value<br />
<code><br />
<br />
Hope it helps... <br />
<br />
<div class="ForumSig"><hr><small>  <font color="Brown" size="2" face="Curier New">I Love T-SQL</font></small><br />
<small>  <font color="Brown" size="1" face="Curier New">"Don't torture yourself,let the life to do it for you."</font></small><br />
</hr></div>

fast search with SQL Server 2000 on varchar column

Tripathi Swati18-Aug-08 22:42

Tripathi Swati

18-Aug-08 22:42

SELECT ROW_NUMBER()
OVER (ORDER BY EmployeeName) AS Row,
EmployeeId, EmployeeName
FROM Employees

Rajkamal_dfine13-Aug-08 9:17

Rajkamal_dfine

13-Aug-08 9:17

Hi

I am in the situation to write code to pull the data from a table which has 1 Million rows per day

what are the optimal ways to search the text ?

database : SQL Server 2000
I have index on the table
The table is portioned

nrk

Re: fast search with SQL Server 2000 on varchar column

Wendelius13-Aug-08 9:41

13-Aug-08 9:41

Hi,

Not necessarily optimal but this may interest you Full-Text Indexes[^]

Another possibility is to create custom keyword based index table using triggers etc

Hope this helps,

MIka

SQL Hosting advice

AlexeiXX313-Aug-08 8:43

AlexeiXX3

13-Aug-08 8:43

Hi, thanks for looking

Im currently hosting my site with godaddy, but ive been having problems when uploading and modifying my DB, since it doesnt allow remote connections from SQL Server Management Studio it only has a webapp (SQL Server web Admin)
My app is a small site, i cant have a dedicated server with them since thats too expensive, i just have a medium plan

I need a hosting company that allows me to connect remotely
Im looking for something not expensive

Im using:
ASP.NET 2.0, AJAX and SQL Server 2005

Please give me some suggestions
Thanks in advance

Alexei Rodriguez

Re: SQL Hosting advice

leoinfo13-Aug-08 9:45

leoinfo

13-Aug-08 9:45

Check Fort Nocs Networks
I know for sure they allow remote connections, but I have no idea about pricing.

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Re: SQL Hosting advice

AlexeiXX313-Aug-08 9:51

AlexeiXX3

13-Aug-08 9:51

Thanks for the reply

They dont show much info or pricings
It must be expensive, it looks like they offer something more than just sql server hosting

Alexei Rodriguez

a qucik question

alexyxj13-Aug-08 7:58

alexyxj

13-Aug-08 7:58

what is the mean of WHILE (1=1) shown in the sample codes as below:

--------------------------
IF (A > B)
A := A + 1;
ELSE
B := B + 1;

WHILE (1 = 1) {
A := A + 1;
}

FOR (declare X any, X := 1; X <= 2 ; X := X + 1){
S := S + X;
}

FOR (declare X any, X := 1; X <= 2 ; ){
S := S + X;
X := X + 1;
}
---------------------------
thanks

PIEBALDconsult13-Aug-08 8:03

PIEBALDconsult

13-Aug-08 8:03

It makes an infinite loop.

Wendelius13-Aug-08 8:04

13-Aug-08 8:04

To define an infinite loop Wink | ;)

Tim Carmichael13-Aug-08 8:06

Tim Carmichael

13-Aug-08 8:06

First, this is the SQL and Database Discussion Forum, not a programming forum.
Second, what do YOU think the WHILE is doing?