Export Data from One server to another server table wise

SSIS and Import/Export
1

Hello experts,
I need to export the data from sql server,
earlier i used Database->Tasks-Export Data...
and select the source and destination, it's working fine.
Now my requirement is, i don't want to copy all data from table, I need only filtered data,
say for example, from table "Sales Shipment Header" i need only where "posted date" > '2016-01-01'

Note: why i used export data wizard, because i need to mapping the fields.
how could i achieve these requirement?

Thanks & Best Regards
Binesh Singh Rajput
(MCTS, MS, MCP)

2

Why don't you execute your Sql query with your WHERE clause and then simply save results as CSV file.extension?

If you must export, another solution would be to save your query result into a table, export that table and delete table after successful export.

3

thank you for your response,
but my requirement is not saving the data to any other format.
i have to mapping fields,
Suppose
in T1 (F1,F2,F3,F4,F5) is fields where as in T2 (F1,F3,F4,F5) field is available.
so i have to map and then copy to other DB Table

4

for mapping, is T1 source table and T2 destination table?

5

I am not sure if I understand exactly what you mean but what I gather is you need to filter from two different tables and then combine the data in to one and then export.

So why don't you do union on two tables and then put it into a new table and then export?

like So:-

with cte as (
Select
[F1],
[F2],
[F3],
[F4],
[F5]
from T1
Union all
Select
[F1],
[F2]= Null,
[F3],
[F4],
[F5]
from T2

) select * into New_table from cte

7

No,
See my query its working, but i need using wizard.
INSERT INTO [server2].[database2].[dbo].[Sales Cr_Memo Line]
([Document No_]
,[Line No_]
,[Sell-to Customer No_]
,[Type]
,[No_]
,[Location Code]
,[Posting Date]
,[Shipment Date])

SELECT FROM [server1].[database1].[dbo].[Sales Cr_Memo Line]
[Document No_]
,[Line No_]
,[Sell-to Customer No_]
,[Type]
,[No_]
,[Location Code]
,[Posting Date]
,[Shipment Date]
WHERE [Posting Date] > '2015-01-01'

8

So after you run this query its is updating [server2].[database2].[dbo].[Sales Cr_Memo Line] then you want to export this, to where ???

INSERT INTO [server2].[database2].[dbo].[Sales Cr_Memo Line]
(
[Document No_]
,[Line No_]
,[Sell-to Customer No_]
,[Type]
,[No_]
,[Location Code]
,[Posting Date]
,[Shipment Date]
)

SELECT
[Document No_]
,[Line No_]
,[Sell-to Customer No_]
,[Type]
,[No_]
,[Location Code]
,[Posting Date]
,[Shipment Date]
FROM [server1].[database1].[dbo].[Sales Cr_Memo Line]
WHERE [Posting Date] > '2015-01-01'

9

no need to export, requirement is only this, copying data from one to other.
but this i want using wizard, why becoz its time consuming,

export data.png1280×1024 89.1 KB

In this wizard i am unable to filter data like [Posting Date] > '2015-01-01'
here if in other server Tables exists then you can't EDIT SQL.
I need here is any way to filter the data.

10

In the wizard, in the step that follows the selection of the source and destination databases, it gives you a choice of copying data from a set of tables/views, or using a query to select the data. You can write a query to select the data including the where clauses you would like to add if you choose that option.