SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 how to copy data into sqlserver table[Query]
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/01/2005 :  03:51:58  Show Profile  Reply with Quote
hi all i got one probelm..


i hav one table in oraclei hav to copy that first 300 records into the sqlserver table through the import/export activites in sqlserver -> enterpriseManager.

can you giv me tha query foor that...task...!!!

thanks
rams123

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/01/2005 :  04:05:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Export 300 data from oracle to text file
Then Use Bulk insert in SQL Server to copy the data

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/01/2005 :  04:42:56  Show Profile  Reply with Quote
hi Madhivanan,

iam select the inport data from the sqlserver enterprise manager and set the all properties..
at select query option i selected ,now i hav to write query for thatcondition(first 300 records)

i.e simply tell me the query for selecting first 300 rows from a table in Oracle....
thats it..!!!!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/01/2005 :  04:46:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Not sure whether this will work

Select * from yourTable where rowid<=300

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 08/01/2005 04:47:06
Go to Top of Page

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/01/2005 :  04:47:48  Show Profile  Reply with Quote
hi

actually the rowid field in oracle is varchar datatype..it wont work..pls
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/01/2005 :  04:52:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No
Rowid is the default column in Oracle Table which is I think numeric
Try that query

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/01/2005 :  04:57:35  Show Profile  Reply with Quote
no it shows that error invalid rowid..
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/01/2005 :  07:33:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
i think this would be a job for OPENQUERY.

see how it works in BOL = Books Online = sql server help

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  08:42:28  Show Profile  Reply with Quote
We use a LINKED SERVER to the Oracle box, and then use OPENQUERY as Spirit suggests - something like:

DECLARE	@strSQL varchar(8000)

SELECT @strSQL = 
'SELECT *
INTO	##MyTempTable
FROM	OPENQUERY(MyOracleLinkedServerName, 
''SELECT *
FROM
(
SELECT	*
FROM	MyOracleTableName
ORDER BY MyColumnA
) X WHERE ROWNUM <= 300
'')'
-- SELECT [SQL] = @strSQL	-- Comment in for debugging
EXEC (@strSQL)

Kristen
Go to Top of Page

chen149
Starting Member

19 Posts

Posted - 08/01/2005 :  10:46:33  Show Profile  Reply with Quote
Select top 50 columnname from tablename
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/01/2005 :  10:48:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>Select top 50 columnname from tablename

Will this work in Oracle?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/01/2005 :  12:11:50  Show Profile  Reply with Quote
"Will this work in Oracle?"

I don't reckon its going to give "the first 300 records" either

Kristen
Go to Top of Page

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/02/2005 :  01:48:04  Show Profile  Reply with Quote
HI KIRSTEN

ITS WORKING FINE SIMPLY I WRITTEN THE QUERY LIKE..

SELECT * FROM MY_TAB WHERE ROWNUM<=300 THATS IT..


THANKS..
RAMS123
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2005 :  02:11:34  Show Profile  Reply with Quote
Might it be an idea to have an ORDER BY on it - so that the 300 that you get is repeatable? (Unless you are happy that they might be different each time, or unless Oracle provides a guaranteed sequence in the absence of an ORDER BY - I'm no Oracle expert!)

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/02/2005 :  05:52:47  Show Profile  Visit spirit1's Homepage  Reply with Quote
i think it does... a voice in the back of my head is saying so...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/02/2005 :  06:10:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>a voice in the back of my head is saying so

Whose voice is it?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/02/2005 :  06:13:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
it's my dark side voice....

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2005 :  06:48:53  Show Profile  Reply with Quote
... not the oracle of Delphi then, eh?
Go to Top of Page

ramana123
Yak Posting Veteran

India
57 Posts

Posted - 08/02/2005 :  07:37:54  Show Profile  Reply with Quote
HI Kristen..
that query returns first 300 rows right.no doubt on that..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000