[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


script to build table in sql server baseds on oracle table

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-05-11
I am trying to build a script that will build a table in my SQL Server 2008 and I wanted to base it on the table that is currently in Oracle

in oracle I can write something like

create table temp1 as
select * from Data_source_table

I want to achieve the same thing, but I want the creation of the table in SQL Server 2008.

This is becasue Oracle will still hold a bunch of business Logic and functions that maniplute the data , and columns on the orcale table may change. So I want to minimize the coding needed when a change occurs in Oracle, I could re-run the script and it would
a) drop the table in SQL SERVER
b) rebuild the table based on the structure in ORACLE
c) populate the table in SQL SERVER based on the date in ORACLE
d) rebuild and run stats on the indexes in SQL SERVER

I am at a loss as to who to do this.

I have built the SQL Server database,

Question by:jhacharya
  • 2
  • 2
LVL 11

Accepted Solution

Lara F earned 1500 total points
ID: 35695076
It is not clear from your question what you need.

In sql server you can create table as

select * into tmpTable from dataSourceTable

If you need transfter data from oracle DB you need to use data import/export wizard


Author Comment

ID: 35695322

Here is the situation:

I have a number of tables that are being developed and poplutated in Oracle. And rather then each time creating a or manually changing the table structure in SQL Server, i wanted to run a script to build tables in SQL Server based on the structer of the tables that had just been developed in ORACLE and then populate the data that is in the Oracle table to the SQL Server table.

This would help me reduce the time it is taking to manually go into the wizad and each time make the adjustments needed and then to repopulate the data.

I am trying to see if there is a script i could run from a dos prompt.

For oracle, I would just write the appropraiate commands in a sql file and then run the following command

sqlplus scott/tiger@test @c:/testfiles/buildtableScrpit.SQL

LVL 11

Assisted Solution

by:Lara F
Lara F earned 1500 total points
ID: 35695372

Author Closing Comment

ID: 35785899
while this did not solve my issue, it did give me other ideas on how ot solve the issue and what not to do, which is just as helpfull

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question