HOW TO EXECUTE 2-3 SQL STATMENTS ONE AFTER ANOTHER BASED ON THE RESULT OF PREVIOUS QUERY?

Question

Post reply

HOW TO EXECUTE 2-3 SQL STATMENTS ONE AFTER ANOTHER BASED ON THE RESULT OF PREVIOUS QUERY?

Learner44

SSCarpal Tunnel

Points: 4466
More actions
June 3, 2013 at 2:12 pm

#273487

Hi,
Ho I can run 2-3 sql statments in one go, which are based on above's query value.
for an example.
first statment copy the data from linked server into new table in local server.
second statement copy few fields from the table , just created in local server to another table in the local server.
and third statment apply the merge query into this table , by considering it as source to the another destination table
now , I want to know how can i achive this in one go? either via stored procdure, cursor or smonthing elese.?
please help.
Thanks.
Pratik.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

Neeraj Dwivedi

SSCertifiable

Points: 6768

June 3, 2013 at 2:18 pm

#1620703

Yes. You can achieve this in one go.

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

prtk.raval (6/3/2013)
Hi,
Ho I can run 2-3 sql statments in one go, which are based on above's query value.
for an example.
first statment copy the data from linked server into new table in local server.
second statement copy few fields from the table , just created in local server to another table in the local server.
and third statment apply the merge query into this table , by considering it as source to the another destination table
now , I want to know how can i achive this in one go? either via stored procdure, cursor or smonthing elese.?
please help.
Thanks.
Pratik.

Sounds to me like a stored proc with 3 steps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 3

can you please describe more..how can i achive this.?

thanks in advance.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

Hi thanks a lot, for your quick replay ..

can you please provide me any related example if you have.?

thanks in advance.

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 5

First 2 statements will be like this. FOr the third.. you have to give more info.

select * into Test from [server].[database].[scheme].MainTable

select col1 into Test1 from Test

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

prtk.raval (6/3/2013)
Hi thanks a lot, for your quick replay ..
can you please provide me any related example if you have.?
thanks in advance.

It is rather difficult to know exactly what you are trying to do but I think you could use the OUTPUT clause.

Something like this:

insert MyTable(Columns)

output inserted.[someColumns]

into MyOtherLocalTable

select [Columns]

from LinkServer.database.schema.table

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 7

Hi,

So my issue is..

I am copying the whole table with OPENQUERY from linked server(oracle) to sql server2008R2 in any temp. table A.

than I am copying just P.K. to another new table B from above Just copied table A.

now I have to apply Incremental ETL by taking table A as source table for Insert and Update and Table B as source table for just delete.

my destination table is in SQL SERVER 2008R2.

All in all i need to perform Incremental ETL, between Linked server and SQL server database tables.

and I don;t want poor performance/more usage of CPU if I run merge query directly to the linked server.

and I also don't want to compare whole table for Delelting the record, just the P.Ks. of Source and Destination tables.

So this all where I am .

Please provide me yoru expert guidance.

thanks.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 8

Hi,

So my issue is..

I am copying the whole table with OPENQUERY from linked server(oracle) to sql server2008R2 in any temp. table A.

than I am copying just P.K. to another new table B from above Just copied table A.

now I have to apply Incremental ETL by taking table A as source table for Insert and Update and Table B as source table for just delete.

my destination table is in SQL SERVER 2008R2.

All in all i need to perform Incremental ETL, between Linked server and SQL server database tables.

and I don;t want poor performance/more usage of CPU if I run merge query directly to the linked server.

and I also don't want to compare whole table for Delelting the record, just the P.Ks. of Source and Destination tables.

So this all where I am .

Please provide me yoru expert guidance.

thanks.

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

prtk.raval (6/3/2013)
Hi,
So my issue is..
I am copying the whole table with OPENQUERY from linked server(oracle) to sql server2008R2 in any temp. table A.
than I am copying just P.K. to another new table B from above Just copied table A.
now I have to apply Incremental ETL by taking table A as source table for Insert and Update and Table B as source table for just delete.
my destination table is in SQL SERVER 2008R2.
All in all i need to perform Incremental ETL, between Linked server and SQL server database tables.
and I don;t want poor performance/more usage of CPU if I run merge query directly to the linked server.
and I also don't want to compare whole table for Delelting the record, just the P.Ks. of Source and Destination tables.
So this all where I am .
Please provide me yoru expert guidance.
thanks.

You lost me a bit in here. There is no MERGE statement in what I posted. There is no delete. It is simply an insert statement with an output clause.

Now since you are using OPENQUERY that will change a bit. 😉 Is this table empty before you insert into it? If not, you will need some way to determine the rows that were just inserted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 10

Sean Lange (6/3/2013)

You lost me a bit in here. There is no MERGE statement in what I posted. There is no delete. It is simply an insert statement with an output clause.
Now since you are using OPENQUERY that will change a bit. 😉 Is this table empty before you insert into it? If not, you will need some way to determine the rows that were just inserted.

The Original Poster has multiple, disconnected posts on the same subject, but hasn't provided ddl/details to really help yet.

basically, he has an oracle linked server, that seems to have a LastModifiedDate column in it.

pseudocode wise, he wants something like this:

SELECT @HighestValue = From MySQLServer
--without copying the entire table to tempdb,
INSERT INTO MySQLServer( --or merge if same data exists, but hasbeen modified)
SELECT * From MyOracleLinkedserver WHERE LastModifiedDate > @HighestValue
--repeat for other tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 11

yup..its totally empty..will automatically create after "Select * into from..." query...

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 12

yes your are right I have one coulmn that indicates timestamp, ineed to use that in order to perform incremental etl with destination table.

thanks.

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

Lowell (6/3/2013)

Sean Lange (6/3/2013)
You lost me a bit in here. There is no MERGE statement in what I posted. There is no delete. It is simply an insert statement with an output clause.
Now since you are using OPENQUERY that will change a bit. 😉 Is this table empty before you insert into it? If not, you will need some way to determine the rows that were just inserted.
The Original Poster has multiple, disconnected posts on the same subject, but hasn't provided ddl/details to really help yet.
basically, he has an oracle linked server, that seems to have a LastModifiedDate column in it.
pseudocode wise, he wants something like this:
SELECT @HighestValue = From MySQLServer
--without copying the entire table to tempdb,
INSERT INTO MySQLServer( --or merge if same data exists, but hasbeen modified)
SELECT * From MyOracleLinkedserver WHERE LastModifiedDate > @HighestValue
--repeat for other tables

Guess I didn't catch the name. I have been involved already with a couple of these other threads.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286573 More actions · Answer 14

Ok so here is the deal. You have now created a number of totally disconnected threads all on the same subject. Myself and others have attempted to assist you but you continue to be elusive and vague and refuse to post any actual details about your tables, the process and what you want. If you are willing to put in some effort to clarify your post I will be happy to help you. If however, you continue to post gibberish then count me out. There are plenty of other people that need my help and are willing to post enough details for me to do that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/