September 29, 2010 at 11:24 am
Hi all,
I having trouble with the following code. It has to update the table fileinfo if it finds filename in table called File; if not it has to insert
I am not able to find any mistake in the following code:
IF EXISTS (SELECT * FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.filename =A.filename )
BEGIN
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.[FileName] =A.[FileName]
END
ELSE IF NOT EXISTS (SELECT * FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.[FileName] =A.[FileName])
BEGIN
insert into dbo.FileInfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from dbo.[File]
END
Code does the first part perfectly but doesn't insert ??
Any suggestions...
Please help
Thanks,
Swetha K
September 29, 2010 at 11:52 am
You may want to try eliminating the second condition and making it a catch-all ELSE.
IF EXISTS (SELECT * FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.filename =A.filename )
BEGIN
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.[FileName] =A.[FileName]
END
ELSE
BEGIN
insert into dbo.FileInfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from dbo.[File]
END
Hopefully that will do. Can't try it here right now.
September 29, 2010 at 11:54 am
You might also want to look into the MERGE statement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 29, 2010 at 11:57 am
I tried doing that .. but wasnt successful..
I am not having any idea of where is it going wrong.. your suggestions would be helpful..
Swetha K
September 29, 2010 at 11:59 am
I tried using Merge statement but because of few columns which has partition imposed on it, Merge statement doesnt allow.
So, I had to use IF ELSE ..
Swetha K
September 29, 2010 at 12:16 pm
Is the INSERT statement attempted, but fails with an error? If so, would you please include the error?
September 29, 2010 at 12:34 pm
Insert statement has no issues only issue is with the else part ..
September 29, 2010 at 12:36 pm
What error did you got i did not get any error
see this
declare @fileinfo table ([FileName] varchar(10),[RowCount] int)
declare @file table ([FileName] varchar(10),[RowCount] int)
insert into @fileinfo
Select 'A',1
insert into @file
Select 'A',3
Select * from @fileinfo
IF EXISTS (SELECT * FROM @fileinfo U INNER JOIN @File A ON U.filename =A.filename )
begin
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM @fileinfo U INNER JOIN @File A ON U.[FileName] =A.[FileName]
end
else
begin
insert into @fileinfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from @File
end
Select * from @fileinfo
Select * from @file
select 'deleted here' /* deleting here fileinfo alone so file value will be there */
Delete from @fileinfo
Select * from @file
IF EXISTS (SELECT * FROM @fileinfo U INNER JOIN @File A ON U.filename =A.filename )
begin
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM @fileinfo U INNER JOIN @File A ON U.[FileName] =A.[FileName]
end
else
begin
insert into @fileinfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from @File
end
Select * from @fileinfo
Select * from @file
hope you will get some idea
Thanks
Parthi
Thanks
Parthi
September 29, 2010 at 1:15 pm
Doesnt give any error but doesnt insert
IF EXISTS (SELECT * FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.filename =A.filename )
BEGIN
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.[FileName] =A.[FileName]
END
ELSE IF NOT EXISTS(SELECT * FROM dbo.[File] A where A.[FileName]not in (select [FileName] from [FileInfo]))
BEGIN
insert into dbo.FileInfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from dbo.[File]
where [FileName]not in (select [FileName] from [FileInfo])
END
September 29, 2010 at 1:21 pm
Your else statement will never find anything.
You are saying if a certain record does not exist then insert it.
IF NOT EXISTS(SELECT * FROM dbo.[File] A where A.[FileName]not in (select [FileName] from [FileInfo]))
BEGIN
insert into dbo.FileInfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from dbo.[File]
where [FileName]not in (select [FileName] from [FileInfo])
END
the select in the insert statement will be an empty result because you made sure it does not exist in the if statement.
IF NOT EXISTS(SELECT * FROM dbo.[File] A where A.[FileName]not in (select [FileName] from [FileInfo]))
_______________________________________________________________
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/
September 29, 2010 at 1:23 pm
I made a few changes to make this easier to follow. It looks like it is working fine to me.
declare @fileinfo table ([FileName] varchar(10),[RowCount] int)
declare @file table ([FileName] varchar(10),[RowCount] int)
--insert into @fileinfo
--Select 'A',1
insert into @file
Select 'B',3
Select [FileName] AS [FileInfo-FileName], [RowCount] AS [FileInfo-RowCount] from @fileinfo
--copied this here to see if the query worked
SELECT * FROM @fileinfo U INNER JOIN @File A ON U.filename =A.filename
IF EXISTS (SELECT * FROM @fileinfo U INNER JOIN @File A ON U.filename =A.filename )
begin
PRINT 'Updating'
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM @fileinfo U INNER JOIN @File A ON U.[FileName] =A.[FileName]
end
else
begin
PRINT 'Inserting'
insert into @fileinfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from @File
end
Select [FileName] AS [FileInfo-FileName], [RowCount] AS [FileInfo-RowCount] from @fileinfo
Select [FileName] AS [File-FileName], [RowCount] AS [File-RowCount] from @file
September 29, 2010 at 1:23 pm
IF EXISTS (SELECT * FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.filename =A.filename )
BEGIN
Select ' First '
UPDATE U SET U.[FileName] = A.[filename],
U.[RowCount] = A.[RowCount]
FROM dbo.fileinfo U INNER JOIN dbo.[File] A ON U.[FileName] =A.[FileName]
END
-- ELSE IF NOT EXISTS(SELECT * FROM dbo.[File] A where A.[FileName]not in (select [FileName] from [FileInfo]))
ELSE
BEGIN
Select 'Second'
insert into dbo.FileInfo([FileName],[RowCount])
select [filename],[ROWCOUNT] from dbo.[File]
where [FileName]not in (select [FileName] from [FileInfo])
END[/code]
What is the output coming if it is second then it is coming in ELSE Part it will insert.
Thanks
Parthi
Thanks
Parthi
September 29, 2010 at 2:09 pm
Hi Parthi,
I have checked your approach but if you can check the following (Please let me know your inputs as I am stuck with the a production update)
It can insert first record but, not the second record....
try giving new data into student and try teh same code it is not inserting the second one ...
CREATE TABLE [dbo].[student]([FName] [varchar](50) NULL,[RowCount] [bigint] NULL)
INSERT into student (FName,[RowCount]) values('skk',4545)
CREATE TABLE [dbo].[Record]([FName] [varchar](50) NULL,[RowCount] [bigint] NULL)
IF EXISTS (SELECT * FROM student s INNER JOIN Record r ON s.FName=r.FName )
begin
UPDATE r set r.[RowCount] = s.[rowcount]
FROM Record r INNER JOIN student s ON s.FName=r.FName
end
IF NOT EXISTS (SELECT * FROM student s INNER JOIN Record r ON s.FName=r.FName )
begin
insert into Record(FName,[RowCount])
select [fname],[ROWCOUNT] from student
end
September 29, 2010 at 2:31 pm
Hi
Can you post sample input what you are giving, so that it would be helpful to sort out the problem
Thanks
Parthi
Thanks
Parthi
September 29, 2010 at 4:15 pm
You were correct SeanLange.
Thank you very much for making it obvious... & Thanks to all who took part in resolving.
Thanks
Swetha K
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply