script to build table in sql server baseds on oracle table

Posted on 2011-05-04
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
    LVL 11

    Accepted Solution

    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

    LVL 2

    Author Comment


    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:Larissa T
    LVL 2

    Author Closing Comment

    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

    The problems with reply email signatures

    Do you wish that you could place an email signature under a reply? Well, unfortunately, you can't. That great Exchange/Office 365 signature you've created will just appear at the bottom of an email chain. What a pain! Is there really no way to solve this? Well, there might be...

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    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…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now