how to copy data into sqlserver table[Query]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to copy data into sqlserver table[Query]

Author

Topic

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-01 : 03:51:58

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

22864 Posts

Posted - 2005-08-01 : 04:05:41

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

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-01 : 04:42:56

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..!!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-01 : 04:46:40

Not sure whether this will work

Select * from yourTable where rowid<=300

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-01 : 04:47:48

hi

actually the rowid field in oracle is varchar datatype..it wont work..pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-01 : 04:52:14

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

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-01 : 04:57:35

no it shows that error invalid rowid..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 07:33:25

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

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 08:42:28

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

chen149
Starting Member

19 Posts

Posted - 2005-08-01 : 10:46:33

Select top 50 columnname from tablename

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-01 : 10:48:12

>>Select top 50 columnname from tablename

Will this work in Oracle?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-01 : 12:11:50

"Will this work in Oracle?"

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

Kristen

ramana123
Yak Posting Veteran

57 Posts

Posted - 2005-08-02 : 01:48:04

HI KIRSTEN

ITS WORKING FINE SIMPLY I WRITTEN THE QUERY LIKE..

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

THANKS..
RAMS123

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 02:11:34

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 05:52:47

i think it does... a voice in the back of my head is saying so...