the I in SSIS

Posted on 2009-12-16
Last Modified: 2012-05-08
I am working between two databases on the same server. I am using SSIS to move data between the two databases -simple example... I am copying a table from Production to Development.
Is there a way from a -Query in SQL server Management Studio -to do a CREATE, DROP, SELECT, UPDATE, INSERT DELETE between databases on the same Server WITHOUT using SSIS?
Question by:dmlyo150

    Expert Comment


    But there are no current available methods to execute those functions without the use of SSIS.

    If you need any further help using it please ask.
    LVL 14

    Expert Comment

    Yes there is an  easy  way using linked server

    LVL 24

    Accepted Solution

    You have made the comment :

    "Is there a way from a -Query in SQL server Management Studio -to do a CREATE, DROP, SELECT, UPDATE, INSERT DELETE between databases on the same Server WITHOUT using SSIS?"

    In that question you say "on the same server".

    If that is the intention, then yes you can do it between databases on the same server, you just reference the fully qualified name:


    Just specify the DATABASE name in the CREATE, DROP, SELECT, UPDATE, INSERT and DELETE statements.
    LVL 2

    Author Comment

    thanks -dbabuck -but Emes' comment brings me to another point of inquiry. I looked around the Internet for sp_addlinkedserver and found how I can query between -not just databases on the same server -but between databases on different servers. however, I am having trouble with the code. can you help me?
    my server name is LALFAPRD (Production)
    with a Database Named (SQLLERIP)
    and my other server name is LALFADEV (Development)
    with a Database Named (SQLLERID)
    if I am working in Development and I want to SELECT from a table in Production, can you help me with the following:

    ...and the other sp_...linked... stored procedures that would help me design the ultimate state of the are SQL Server data source.
    thanks. -
    LVL 14

    Assisted Solution

    EXEC sp_addlinkedserver
    @server='Linked', -- local SQL name given to the linked server
    @provider='sqlncli', -- OLE DB provider (the .2 means the SQL2K version)
    @datasrc='LALFAPRD', -- analysis server name (machine name)
    @catalog='SQLLERID', -- default catalog/database
    @srvproduct='' -- not used but needed here

    set this call from your dev sql server
    LVL 24

    Expert Comment

    by:DBAduck - Ben Miller
    Aside from that you can do this, I would not necessarily recommend setting up that kind of relationship as Development servers having access to production is a very nice security hole.

    But Emes has provided you with the script to help you get this done if that is the direction that you want to go in.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article I will describe the Backup & Restore 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.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now