August 9, 2010 at 9:52 am
I'd like to write a procedure that will take the data from an 'Import' table with Select (Id, Name, City, Salary) and update / insert that data in another table 'MonthsImports'. Need Help Please..
CREATE PROCEDURE p_MonthImports
AS BEGIN
DECLARE id INT
DECLARE name NVARCHAR(10)
DECLARE salary INT
DECLARE city NVARCHAR(10)
DECLARE cur_client CURSOR FOR
SELECT id,name,salary,city from Import
OPEN cur_client
..............
...........
August 9, 2010 at 10:36 am
You haven't given enough information. Check the following link on how to help people answer your question.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 9, 2010 at 11:47 am
It appears you want to do the insert & update through a single query. You can't do that in SQL 2005. You will have to identify a logic to find which rows to insert and which ones to update. And you won't need a cursor to insert or update multiple rows.
Also, I agree with @drew.allen; you should provide more information.
August 9, 2010 at 12:51 pm
rjv_rnjn (8/9/2010)
It appears you want to do the insert & update through a single query. You can't do that in SQL 2005. You will have to identify a logic to find which rows to insert and which ones to update. And you won't need a cursor to insert or update multiple rows.Also, I agree with @drew.allen; you should provide more information.
While you can't do that in 2K5 in one specific SQL statement, you can do both within one block of code in a stored procedure.
(And you can do it in one statement in 2K8 with MERGE.)
August 9, 2010 at 1:10 pm
bteraberry (8/9/2010)
While you can't do that in 2K5 in one specific SQL statement, you can do both within one block of code in a stored procedure.
Agreed; didn't want to convey the opposite but should have been more specific.
August 10, 2010 at 1:01 am
OK. Every month the Import table is filled with the current information about the salaries. I have to store the whole history concerning the salaries in another table. For this purpose, I need to write a stored procedure that will Select all the records and fields from the monthly Import table and Update or Insert 'MonthsImports' where the History will be stored.
Import:
id | name | salary | city
August 10, 2010 at 1:15 am
in oracle I use:
merge into MonthsImports MI using (select...) I on MI.id = I.id
when matched then update set MI.id = I.id ...
when not matched then Insert MI.id... values(I.id...)
I dont have experience with MS SQL Procedures..
August 10, 2010 at 8:09 am
transfering (8/10/2010)
in oracle I use:merge into MonthsImports MI using (select...) I on MI.id = I.id
when matched then update set MI.id = I.id ...
when not matched then Insert MI.id... values(I.id...)
I dont have experience with MS SQL Procedures..
MS SQL stored procedures are not very different from Oracle SP's, give and take a few things like no ref cursors required to return resultsets. Here: http://technet.microsoft.com/en-us/library/bb510625.aspx you will find an example of insert & update with & without "merge". As pointed out by @bteraberry "merge" was introduced with SQL 2008.
For a simple explanation of SP's take a look here: http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx
August 10, 2010 at 9:07 am
transfering (8/9/2010)
I'd like to write a procedure that will take the data from an 'Import' table with Select (Id, Name, City, Salary) and update / insert that data in another table 'MonthsImports'. Need Help Please..CREATE PROCEDURE p_MonthImports
AS BEGIN
DECLARE id INT
DECLARE name NVARCHAR(10)
DECLARE salary INT
DECLARE city NVARCHAR(10)
DECLARE cur_client CURSOR FOR
SELECT id,name,salary,city from Import
OPEN cur_client
..............
...........
1. Unlike to Oracle, you shoould not use cursor for this sort of task (and many other kind of tasks) in SQLServer.
2. MERGE statement is the new one in SQL2008 but it is not available in SQL2005
In SQL2005 you should do it in two steps:
1. Perform Update for existing records
2. Perform Insert for new records
NO CURSORS! Something like:
-- 1. Update existing records
UPDATE MI
SET Name = I.Name
,City = I.City
,Salary = I.Salary
FROM MonthsImports AS MI
JOIN Import AS I
ON I.Id = MI.Id
-- 2. Insert new records
INSERT MonthsImports (Id, Name, City, Salary)
SELECT I.Id, I.Name, I.City, I.Salary
FROM Import AS I
LEFT JOIN MonthsImports AS MI
ON MI.Id = I.Id
WHERE MI.Id IS NULL
Please Note! The above is the best I can give you based on the minimal details provided by you.
Want better answer? Please follow the link in my signature...
Full DDL for your tables would be very helpfull here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply