Urgent need for a query

  • Hi Gurus,

                I have a question I want to copy a database table from remote server. I give query in query analyzer "select * into receipt from sw8.PRD1.wh1.receipt" it copies all the data from remote location to my database table but the table that is created on my end doesn't have the constraints that I found in Remote server. Is there anyway to copy the same structure means all constraints and other things I found through Query in Query Analyzer. Please help me its Urgent.

                                                       Million of Thanks in advance.

                                                              Manutosh

  • I do not think "select into" can create constraints as well. It can only get data.

    The best option for you will be to first script your table and create them at destination and then use a simple select to push data into the destination table, something like this:

    insert into sw8.PRD1.wh1.receipt select * from my_table

    Hope this helps,

    Ganesh

  • Try using DTS. There is a copy SQL objects tool that will do what you want and has all the options for you to turn on and off.

    *IMPORTANT NOTE: Turn off the "include depended objects" unless you also want to copy all views, stored procedures etc. I wiped out a production db in my early days by try to copying a stored procedure to it with that turned on. It pulled the dev tables it referenced along with it. Doh!!

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • I don't wanna use DTS tool. I just want to do that with "select * into" clause.If there is no choice with this clause I got down.

                                         Thanks for your help

  • Ok then ganeshmuthuvelu's advice will do. You can preface it with a statement that checks if the table exists and if not create it before you do you select into. Do you know how to do that?

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply