Copy Selected Records from Another Server/Databse

[Stangleboy]

I am trying to copy select records from a table on another server to my local computer using MS SQL Server Management Studio for SQL Server 2008 R2. The table does not exist on my machine and I would like to create and then copy the table structure (indexes, etc.) and table data. I only have read rights to the host server and cannot make views, but I can make temporary tables if this helps. I have been searching the forum/web and found pieces here and there but I cannot seem to incorporate them together to get my end results. One issue is that there are over 11millions records and 42 fields that are in the table so when I try to use the “Export/Import” function the process dies on my computer (and I have a quad core 64-bit system). Any help will be greatly appreciated.

My goals are:
1. Have it run as one script.
2. Create the new table in the database.
3. Copy table structure and data for select records (see code below).

This host database name is “AH1PD01” and I have the server IP if needed for the code (Ithink this is my main issue is writing the code to use the full server name and database name correctly). My database name on my local machine is “DataRep”. Thank you again in advance.

“SELECT DISTINCT
PARTICIPANT_DEMOGRAPHIC.*
FROM
CASE_CAS As CC INNER JOIN
CASE_PARTICIPANT As CP ON CC.C_CASE_ID = CP.CP_CASE_ID INNER JOIN
PARTICIPANT_DEMOGRAPHIC As PD ON CP.CP_PRTCP_ID = PD.P_PRTCP_ID
WHERE
CASE_CAS.C_MNG_CNTY_FIPS_CD = '071'
AND CASE_CAS.C_PND_CLS_STAT_CD <> 'ACT'
AND CASE_CAS.C_CRNT_STAT_CD = 'OPN'”

 

[genomon]

Is there any reason you cannot add the server as a linked server to your SSMS?

Beir bua agus beannacht!

 

[Stangleboy]

I usually have a DBA handle this, so I am not familiar with that process, I am always willing to try. Do you know where I can find of to do that? Thanx!!

 

[genomon]

There are different ways that amount to the same thing. One is to click on "Server Objects" in Object Explorer under an existing connection. Right-click on Linked Servers, then New to open the wizard. Another is to click on the Registered Servers icon in the toolbar, click on the Database Engine icon, right-click on Database Engine icon, click on new group or registration to open the wizard. If this is a bit confusing, drag your DBA over and make him do it - after all, it's HIS job anyway!
Good luck!

Beir bua agus beannacht!

 

[Stangleboy]

The DBA is no longer with us hopefully we get another soon with a new budget year, I did both and get denied due to rights.