January 6, 2015 at 1:00 am
Hi All,
I am new to SQL and Stored Procedure. Please help on the below scenario. An abstract example is enough,
I have four tables.
1. Account(Account no, mode,value,customer,contact)
2. AccountTemp(Account no, mode,value,customer,contact)
3. AccountChk(Account no,mode)
4. AccountMock((Account no, mode,value,customer,contact)
I have to write a stored procedure to load Account and AccountMock tables with data from AccountTemp.
The condition is if the "Account no and mode" columns in AccountTemp is present in AccountChk, then that row has to go to AccountMock table. If the "Account no and mode column " in AccountTemp is not present in AccountChk, then then that row has to go to Account table.
Please suggest me an example on how to write a stored procedure for this scenario.
January 6, 2015 at 1:38 am
Table definitions (CREATE TABLE ...), sample data (INSERT INTO ...) and expected output please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 2:03 am
Hi Gila,
Please suggest where to put condition in my stored procedure,
Entire Record from AccountTemp has to go into one of the two tables Account and AccountMock based on one condition, if an account no and mode for a particular row in Temp table matches with AccountChk table, then that row has to go to AccountMock otherwise it should go to Account .
Below is what i tried,
CREATE PROCEDURE [dbo].[AccountTemp ] (
@account no
@mode
)
AS
INSERT into dbo.AccountMock
WHERE [Account no] = @account no AND
[mode] = @mode
INSERTinto [dbo].[Account]
WHERE [Account no] != @account no AND
[mode] != @mode
END
Will this load all rows from AccountTemp to Account and AccountMock based on the condition.
January 6, 2015 at 2:18 am
No, it won't (won't even compile)
Table definitions (CREATE TABLE ...), sample data (INSERT INTO ...) and expected output please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 3:13 am
Hi Gila,
please find the below things,
ex.
Accounttemp
Accountno mode value customer contact
950017 011 78783 Fring&co 950022333
****************************************************
Account
Accountno mode value customer contact
450037 015 5783 Test&co 250024343
454532 012 67343 Company2 24323323
**********************************************************
AccountMock
450023 013 5784 company5 25001273
***************************************************************
Accountchk
950017 011 78783 company6 898328392
**********************************************************
so while running the Store procedure while having the above datas, row from AccountTemp table has to be inserted into AccountMocktable because first two columns of AccountTemp's first row matches with Accountchktable's first two columns.
This is the scenario Gila, If a particular Accountno and mode is present in Accountchktable then that has to load into AccountMocktable otherwise it has to go to Account table.
January 6, 2015 at 3:42 am
If you don't want a tested solution, fine (I'm not spending extra time trying to work out data types and write up insert statements)
Something like
INSERT INTO AccountMocktable <column list>
SELECT <column list> FROM AccountTemp at
WHERE EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
INSERT INTO Account <column list>
SELECT <column list> FROM AccountTemp at
WHERE NOT EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 4:21 am
Hi Gila,
Im sorry, I am new to SQl that's why posted wrongly.
Here is my Table Creation
CREATE TABLE [dbo].[Account](
[Accountno] [nchar](10) NOT NULL,
[mode] [nchar](25) NULL,
[value] [nchar](25) NULL,
[customer] [nchar](15) NULL,
[contact] [nchar](25) NULL,
)
GO
I have tried your command in SQL server it is throwing error .
CREATE PROCEDURE sp3
AS
INSERT INTO AccountMock <Accountno,mode,value,customer,contact>
SELECT <Accountno,mode,value,customer,contact> FROM AccountTemp at
WHERE EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
INSERT INTO Account <Accountno,mode,value,customer,contact>
SELECT <Accountno,mode,value,customer,contact> FROM AccountTemp at
WHERE NOT EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
GO
Error
Msg 102, Level 15, State 1, Procedure sp3, Line 5
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Procedure sp3, Line 9
Incorrect syntax near '<'.
Please advise .....
January 6, 2015 at 4:31 am
Um.......
Replace <column list> with the list of columns. It's a place holder.
And open up Books Online (F1 from in Management Studio) and read over basic SQL syntax for selects and inserts.
NChar? Really?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 4:44 am
Hi Gila,
Could you please throw some light on this and make this query workable.
Please advise where im making the mistake.
CREATE PROCEDURE sp3
AS
INSERT INTO AccountMock (Accountno,mode,value,customer,contact)
SELECT (Accountno,mode,value,customer,contact) FROM AccountTemp at
WHERE EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
INSERT INTO Account (Accountno,mode,value,customer,contact)
SELECT (Accountno,mode,value,customer,contact) FROM AccountTemp at
WHERE NOT EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
GO
*************************************************
error
Msg 102, Level 15, State 1, Procedure sp3, Line 6
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp3, Line 10
Incorrect syntax near ','
January 6, 2015 at 4:54 am
Open up Books Online. Look at the syntax for a SELECT. Look at the examples on the page. Look at your selects and see what's different.
Edit: And buy yourself a copy of T-SQL Fundamentals and read it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 5:08 am
Let's try two things. First, qualify all the column names. You've created aliases, at, am, put those in front of the columns where appropriate so that all the columns are marked by the table that they belong to. This is a common best practice. Let's also lose the parenthesis around the columns in the SELECT list. Then see how it runs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 5:21 am
Hi Gila,
Thanks, I have found the mistake, Sorry im troubling for small syntax errors too.
This works fine .
CREATE PROCEDURE sp4
AS
INSERT INTO AccountMock (Accountno,mode,value,customer,contact)
SELECT Accountno,mode,value,customer,contact FROM AccountTemp at
WHERE EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
INSERT INTO Account (Accountno,mode,value,customer,contact)
SELECT Accountno,mode,value,customer,contact FROM AccountTemp at
WHERE NOT EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
GO
But this query always loads Account even if the particular Accountno and mode is present in AccountChk. If it is present it AccountChk then that row has to go to Mocktable instead of the actual account table.
Please advise what is the logical error
January 6, 2015 at 5:25 am
Replace AccountMock with AccountChk in both subqueries. I got the table names mixed up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2015 at 5:42 am
Hi Gila,
Thanks , I found the mistake and its working now
CREATE PROCEDURE sp5
AS
INSERT INTO AccountMock (Accountno,mode,value,customer,contact)
SELECT Accountno,mode,value,customer,contact FROM AccountTemp at
WHERE EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
INSERT INTO Account (Accountno,mode,value,customer,contact)
SELECT Accountno,mode,value,customer,contact FROM AccountTemp at
WHERE NOT EXISTS (SELECT 1 FROM AccountMock am where at.Accountno = am.Accountno AND at.mode = am.mode)
GO
But while running this sql, all data from Temp is going to Account table even if the particular account no and mode is present in the AccountChk table. It does not go to Mock table at all. The condition if the particular record matches with AccountChk then it has to go into AccountMock table,
but this query always loads Account table.
Please advise what is logically wrong.
January 6, 2015 at 5:44 am
Sorry for updating again, I didnt see the second page, I thought my post is not updated.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply