July 14, 2010 at 7:05 pm
Paul,
You are awesome. Thanx for that code...I never knew of those syntaxes in SQL. I am going to use them for my code.
Steve, thanks a lot for the inputs.:)
You guys are really helpful!
July 14, 2010 at 8:58 pm
Hi,
1) Can anyone tell me this difference in code?
Update Event_Person_Info
SET Cert_Status = Person_Info.Cert_Status
FROM Person_Info --Line 3
WHERE (Event_Person_Info.Names = Person_Info.Names AND Event_Person_Info.Country = Person_Info.Country AND Person_Info.Cert_Status = 'Y')
Update Event_Person_Info
SET Cert_Status = Person_Info.Cert_Status
FROM Event_Person_Info, Person_Info --This is the change in code.
WHERE (Event_Person_Info.Names = Person_Info.Names AND Event_Person_Info.Country = Person_Info.Country AND Person_Info.Cert_Status = 'Y')
2)I have also noticed if I remove Line 3 of the code above, I get an error, "The multi-part identifier "Person_Info.Cert_Status" could not be bound." Why should we put a 'FROM' after keyword 'SET'? Is that the normal way to use 'SET'.
Thanks!
July 14, 2010 at 11:24 pm
tasnim.siddiqi (7/14/2010)
Can anyone tell me this difference in code?
They're pretty much equivalent, though it is considered better to use aliases and explicit joins to make the intention clearer. I would write that update as:
UPDATE EPI -- The table to update, as an alias
SET Cert_Status = PRI.Cert_Status -- The column to update
FROM Event_Person_Info EPI -- The source table
JOIN Person_Info PRI -- Second source table
ON EPI.Names = PRI.Names -- Join condition #1
AND EPI.Country = PRI.Country -- Join condition #2
WHERE PRI.Cert_Status = 'Y'; -- Condition for the update to take place
The FROM clause (as a whole, including the JOIN and WHERE clause) identifies the source of the data to update.
The UPDATE and SET reference columns and the alias from the source.
See the following link for syntax details and further examples:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 15, 2010 at 12:01 am
Thanks Paul!
July 15, 2010 at 8:13 am
repeatedly adding a random offset (> 0) to an initial seed value
will give a randomised sequence of unique integers
something like:
create table #temp (
[rid] int
identity
, [seed] int
constraint PK_temp
primary key nonclustered
)--create table
--
declare @seed int
declare @loop int
--
set @seed = 100
set @loop = 10
--
while (@loop > 0) begin
set @loop = @loop - 1
set @seed = @seed + 1 + cast(rand() * 10 as int)
insert into #temp ([seed]) values (@seed)
end
--
select *
from #temp
select @seed = max([field]) from existing_table
then merge the temp table into the target table with row_number() or something
July 20, 2010 at 12:55 am
Hey,
Can you tell me why do I get an error for the following?
Column name have been declared as Version Number. --This is fine.
When writing stored procedure,
Set Version Number = Something
I get an error related to 'Number'.
How can I overcome this problem?
July 20, 2010 at 2:24 am
If the column name contains a space, use square brackets around it: [Version Number]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2010 at 9:42 pm
Hi Paul,
Thanks for the earlier replies.
A new issue just arose. I am getting the following error, when I am dealing with a large chunk of data.
Msg 512, Level 16, State 1, Procedure stored_proc2, Line 24
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(1 row(s) affected)
CAn you tell me why this is the case and how can I overcome this?
July 20, 2010 at 10:43 pm
This demonstrates one way the problem can arise:
-- Demo tables
DECLARE @T1 TABLE (A INTEGER NULL);
DECLARE @T2 TABLE (B INTEGER NULL);
-- Add rows with values (1, 2, 3) to table A
INSERT @T1 (A) VALUES (1);
INSERT @T1 (A) VALUES (2);
INSERT @T1 (A) VALUES (3);
-- Add a single row to table B
INSERT @T2 (B) VALUES (1);
-- Select rows from A
SELECT *
FROM @T1
WHERE A =
(
-- Subquery
-- This is OK because only one row exists in B
SELECT *
FROM @T2
);
-- Add an extra row to table B
INSERT @T2 (B) VALUES (2);
SELECT *
FROM @T1
WHERE A =
(
-- Subquery
-- Error: Subquery returned more than 1 value.
SELECT *
FROM @T2
);
If that doesn't directly answer your question, post the part of your procedure code that's causing the problem.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2010 at 11:16 pm
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[stored_proc2]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--This part returns those entries/rows that are not present in the MAL but is present in the Temp. Events Table
--After the unique rows are returned, they are stored into a temporary table.
INSERT INTO Master_Application_List
SELECT distinct Discovered_App_Name, Version_Number, AIT_Number, Cert_Status, Cert_Date, App_Status, Remediation FROM Temp_Event_Import WHERE NOT EXISTS (SELECT * FROM Master_Application_List WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number))
--This adds the App_IDs back to Temp. Event Import Table. Corresponding App_ID added to the respective row.
Update Temp_Event_Import
Set App_ID = (select App_ID from Master_Application_List where Temp_Event_Import.Discovered_App_Name = Master_Application_List.AppName AND Temp_Event_Import.Version_Number = Master_Application_List.AppVer AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number)
--Check if Important fields are ok.
--Cert_Status. Where clause contains the condition for the Update
Update Master_Application_List
SET Cert_Status = Temp_Event_Import.Cert_Status
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Master_Application_List.Cert_Status IS NULL OR (Temp_Event_Import.Cert_Status = 'Y' AND Master_Application_List.Cert_Status = 'N')))
--Cert_Date
Update Master_Application_List
Set Cert_Date = Temp_Event_Import.Cert_Date
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Temp_Event_Import.Cert_Date > Master_Application_List.Cert_Date OR Master_Application_List.Cert_Date IS NULL))
--App_Status
Update Master_Application_List
Set App_Status = Temp_Event_Import.App_Status
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND ((Master_Application_List.App_Status IS NULL) OR NOT (Temp_Event_Import.App_Status IS NULL OR Master_Application_List.App_Status IS NULL)))
--Remediation
Update Master_Application_List
SET Remediation = Temp_Event_Import.Remediation
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Master_Application_List.Remediation IS NULL OR (Temp_Event_Import.Remediation = 'Y' AND Master_Application_List.Remediation = 'N')))
END
Error Messages:
Msg 512, Level 16, State 1, Procedure stored_proc2, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(1 row(s) affected)
The above is the stored procedure I am executing. Thing is Master_Application_List is a table that has 95000 of datas. Temp_Event_Import table has around 200 datas. Idea is to check is temp datas are in Master_App_List. If not present then add the entry. If present then check columns and update since, temp datas are the most updated. The code works when I tried with just a few data entries, I added myself, just for testing. It meets the objective atleast. But now, when I import 95000 datas etc. it this error is shown.
Any idea how I can overcome this problem?
PS: ur awesome dude!
July 21, 2010 at 12:30 am
There are a few things to sort out there - could you please post the CREATE TABLE statements for each of the tables involved? It would also help a lot if you could script out the indexes and provide the INSERT statements to add the 'few rows' of test data.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 21, 2010 at 11:21 pm
Hi Paul,
The tables were created manually. Only AppID column was created using code as it was declared as int primary key identity in Master_Application_List table. App_ID declared as foreign key in Temp_Event_Import table. Here are the table details:
Master_Application_List:
AppNamevarchar(MAX)Checked
AppVervarchar(MAX)Checked
AIT_IDvarchar(MAX)Checked
Cert_Statusvarchar(MAX)Checked
Cert_Datevarchar(MAX)Checked
App_Statusvarchar(MAX)Checked
Remediationvarchar(MAX)Checked
App_IDintUnchecked
Temp_Event_Import Table:
Discovered_App_Namevarchar(MAX)Checked
Version_Numbervarchar(MAX)Checked
AIT_Numbervarchar(MAX)Checked
Event_IDvarchar(MAX) Checked
Cert_Statusvarchar(MAX)Checked
Cert_Datevarchar(MAX)Checked
App_Statusvarchar(MAX)Checked
Remediationvarchar(MAX)Checked
App_IDintChecked
PS: checked means Null is allowed
2) Here is the stored_procedure.
INSERT INTO Master_Application_List
SELECT distinct Discovered_App_Name, Version_Number, AIT_Number, Cert_Status, Cert_Date, App_Status, Remediation FROM Temp_Event_Import WHERE NOT EXISTS (SELECT * FROM Master_Application_List WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number))
Update Temp_Event_Import --This is the part giving errors.
Set App_ID = (select App_ID from Master_Application_List where Temp_Event_Import.Discovered_App_Name = Master_Application_List.AppName AND Temp_Event_Import.Version_Number = Master_Application_List.AppVer AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number)
Update Master_Application_List
SET Cert_Status = Temp_Event_Import.Cert_Status
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Master_Application_List.Cert_Status IS NULL OR (Temp_Event_Import.Cert_Status = 'Y' AND Master_Application_List.Cert_Status = 'N')))
Update Master_Application_List
Set Cert_Date = Temp_Event_Import.Cert_Date
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Temp_Event_Import.Cert_Date > Master_Application_List.Cert_Date OR Master_Application_List.Cert_Date IS NULL))
Update Master_Application_List
Set App_Status = Temp_Event_Import.App_Status
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND ((Master_Application_List.App_Status IS NULL) OR NOT (Temp_Event_Import.App_Status IS NULL OR Master_Application_List.App_Status IS NULL)))
Update Master_Application_List
SET Remediation = Temp_Event_Import.Remediation
FROM Master_Application_List, Temp_Event_Import
WHERE (Master_Application_List.AppName = Temp_Event_Import.Discovered_App_Name AND Master_Application_List.AppVer = Temp_Event_Import.Version_Number AND Master_Application_List.AIT_ID = Temp_Event_Import.AIT_Number AND (Master_Application_List.Remediation IS NULL OR (Temp_Event_Import.Remediation = 'Y' AND Master_Application_List.Remediation = 'N')))
END
July 22, 2010 at 2:28 am
You can script the table definitions out using Management Studio 🙂
It will also script the data if you set the options right.
Otherwise, it's just too much work to try to help you here - I only have so much free time 🙁
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 2:36 am
USE [Tasnim]
GO
/****** Object: Table [dbo].[Master_Application_List] Script Date: 07/22/2010 16:35:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Master_Application_List](
[AppName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AppVer] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AIT_ID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Date] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Remediation] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_ID] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[App_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Tasnim]
GO
/****** Object: Table [dbo].[Temp_Event_Import] Script Date: 07/22/2010 16:36:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Temp_Event_Import](
[Discovered_App_Name] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Version_Number] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AIT_Number] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Event_ID] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cert_Date] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Status] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Remediation] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_ID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Tasnim]
GO
ALTER TABLE [dbo].[Temp_Event_Import] WITH CHECK ADD FOREIGN KEY([App_ID])
REFERENCES [dbo].[Master_Application_List] ([App_ID])
July 22, 2010 at 2:45 am
Thanks. If you could provide a few rows of INSERT statements for the test data that would be great too.
(Right-click the database, Generate Scripts...and make sure you select Script Data = true in the Wizard that appears).
Going to watch a film now, but I'll be back later.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 66 total)
You must be logged in to reply to this topic. Login to reply