As far as I see, there are mainly three kinds of ways for you to cope with your problem:
1) You can also use a very long (maybe) sql statement with the help of SqlCommand+SqlConnection, but please use ";" to seperate your SqlStatement, something like this below:
using (SqlCommand cmd = new SqlCommand("sql1;sql2,……,sqlN",new SqlConnection("Connection string")))
{
//Do what you wanna
}
2) [Recommanded] you can write a Stored Procdure to combine what you wanna do together inside, and then call to execute it.
3) Another choice that you can still use Trigger to cope with that.
the reason is i have 2 table and want to insert the combination of both table into 3rd table. which is not possible by any select statement.so i will fetch values from these table using loop and insert it into 3rd table.
I see two possibilities here.
You create a temp table, insert data into it , select * it, drop it
CREATE TABLE #IN_MEMORYTEMP_TABLE (
COL1 VARCHAR(50),
COL2 INT,
COL3 VARCHAR(50)
);
INSERT INTO #IN_MEMORYTEMP_TABLE (COL1, COL2, COL3) SELECT COL_X , COL_Y, COL_Z FROM DATABASE_NAME1.[SCHEMA].TABLE_NAME1 WHERE <CONDITION1>
INSERT INTO #IN_MEMORYTEMP_TABLE (COL1, COL2, COL3) SELECT COL_P , COL_Q, COL_R FROM DATABASE_NAME2.[SCHEMA].TABLE_NAME2 WHERE <CONDITION2>
SELECT * FROM #IN_MEMORYTEMP_TABLE
---IT IS A GOOD PRACTICE IMO, TO DROP THE TABLE IN ORDER TO RELEASE THE MEMOERY
DROP TABLE #IN_MEMORYTEMP_TABLE
Creating a temp table may not be needed. Both SELECT statements can be combined using UNION ALL.
I hope that this helps you.
Thanks,
-Aarsh, MCTS
Please mark posts as answers those help you / answer your question.
soft2creativ...
Member
21 Points
31 Posts
LOOP IN SQL SERVER STORED PROCEDURE
Dec 06, 2012 11:53 AM|LINK
hello i want to run a nested loop in stored procedure
the reason is i have 2 table and want to insert the combination of both table into 3rd table. which is not possible by any select statement.
so i will fetch values from these table using loop and insert it into 3rd table.
Harsh Mittal
8053536111
9354891238
info@soft2creative.com
Rajneesh Ver...
All-Star
37372 Points
6846 Posts
Re: LOOP IN SQL SERVER STORED PROCEDURE
Dec 06, 2012 12:43 PM|LINK
See:
http://www.learningcomputer.com/sqlserver/sql_stored_procedures.html
http://sqlusa.com/bestpractices2005/doublecursor/
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LOOP IN SQL SERVER STORED PROCEDURE
Dec 07, 2012 01:54 AM|LINK
Hello
As far as I see, there are mainly three kinds of ways for you to cope with your problem:
1) You can also use a very long (maybe) sql statement with the help of SqlCommand+SqlConnection, but please use ";" to seperate your SqlStatement, something like this below:
2) [Recommanded] you can write a Stored Procdure to combine what you wanna do together inside, and then call to execute it.
3) Another choice that you can still use Trigger to cope with that.
aarsh
Participant
1543 Points
428 Posts
Re: LOOP IN SQL SERVER STORED PROCEDURE
Dec 07, 2012 03:23 AM|LINK
I see two possibilities here.
I hope that this helps you.
Thanks,
-Aarsh, MCTS
Please mark posts as answers those help you / answer your question.