August 18, 2015 at 3:49 pm
I am running a query and stored the result set ( multiple columns as full resultset rows ) in a variable ( query has only read access , so i can write the data to a table and read it). My question is how to read or write that data to a table on another server?
August 18, 2015 at 10:34 pm
I did not understand properly , but let me know did you tried using for each loop to iterate your result set , then execute sql task does the insert into another table .
August 19, 2015 at 1:22 am
Rather than ExecuteSQL, why not use a standard dataflow with suitable source/destination components?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 19, 2015 at 8:40 am
The query has parameters and create and update statements in it and parameters , when i try to run the query in OLEDB sql command source , the parameters are not recognized. Also the query which i run is on the other source server , where i have no write access.
August 19, 2015 at 8:42 am
Something like below.
DECLARE @ReportStartDate DATE = '07/01/2015'
DECLARE @ReportEndDate DATE = '07/01/2015'
--staging tenp table creation
IF OBJECT_ID('tempdb..#FinalOutPut') IS NOT NULL
BEGIN
DROP TABLE #FinalOutPut
END
CREATE TABLE #FinalOutPut
(
Asdfg VARCHAR(60)
,M_id VARCHAR(100)
,GP_id INT
,C_id int
,Dt_modified DATETIME
,UID VARCHAR(50)
)
IF OBJECT_ID('tempdb..#EC') IS NOT NULL
BEGIN
DROP TABLE #EC
END
SELECT DISTINCT
M_id,
C_GID,
INTO#EC
FROME_COv WITH(NOLOCK)
UPDATE E
SET E.plan_gid = E_PARENT.plan__gid
FROM #EC E
JOIN #EC E_PARENT
ON E.C_GID = E_PARENT.C_GID
AND E_PARENT.parent_gid = E_PARENT.C_GID
ANDE.Dt_modified BETWEEN E_PARENT.Dt_modified AND E_PARENT.last_touch
AND E.plan_gid= -1
CREATE CLUSTERED INDEX IX_EC ON #EC(C_GID,parent_gid,effective_date,termination_date,default_lob)
--transactions
--DOB Change Transaction starts here
;WITH Chng_Seq
AS (
SELECT *
FROM DBO.Contacts C WITH (NOLOCK)
WHERE CAST(C.last_touch AS DATE) BETWEEN @ReportStartDate AND @ReportEndDate
)
INSERT INTO #FinalOutPut
SELECT *
FROM A
JOIN B ON B.Rownum = A.Rownum + 1
WHERE A.birth_date <> B.birth_date;
;WITH Chng_Seq
AS (
SELECT *
FROM DBO.Contacts C WITH (NOLOCK)
WHERE CAST(C.last_touch AS DATE) BETWEEN @ReportStartDate AND @ReportEndDate
)
INSERT INTO #FinalOutPut
SELECT *
FROM C
JOIN D ON B.Rownum = A.Rownum + 1
WHERE C.birth_date <> D.birth_date;
SELECT DISTINCT
*
INTO #Summarytable
FROM #FinalOutPut A
SELECT *
FROM #Summarytable
WHERE IState = 'IL'
SELECT *
FROM #Summarytable
WHERE IState = 'TX'
SELECT *
FROM #Summarytable
WHERE IState = 'OK'
SELECT *
FROM #Summarytable
WHERE IState = 'MT'
SELECT *
FROM #Summarytable
WHERE IState = 'NM'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply