May 20, 2010 at 3:46 am
Hi,
Table_A has many columns which are grouped by a File_Name column. I need to insert one or more rows into Table_B based on the File_Name. There is a unique (Id) column. Each time a set of rows are loaded into Table_B the Load_Id field (Bit) needs to be set to 1.
Table_A
Id File_Name Load_Id
1, 1406857_20100517.xml, 0
2, 1406857_20100517.xml, 0
4, 1406859_20100517.xml, 0
5, 1406859_20100517.xml, 0
6, 1406859_20100517.xml, 0
7, 1117523_20100518.xml, 0
8, 1117527_20100518.xml, 0
9, 1117527_20100518.xml, 0
10, 1117527_20100518.xml, 0
11, 1252421_20100518.xml, 0
14, 1252337_20100518.xml, 0
15, 1252337_20100518.xml, 0
16, 1515428_20100518.xml, 0
17, 1515428_20100518.xml, 0
18, 1531215_20100518.xml, 0
19, 1531215_20100518.xml, 0
In the 1st instance rows 1 & 2 need to be inserted into Table_B and their Load_Id's set to 1
Next, rows 3,4 & 5 need to inserted and their Load_Id's set to 1
Next, row 7 etc..
Any ideas please?
May 20, 2010 at 6:21 am
No Ideas. Create table & data population scripts together with better explanation of expected results would help to generate some...
Your expected results are not very clear: what you refer as rows 1,2 and 3,4,5? Are these IDs of the records from table_A? All of them contain referenece to the same file name....
May 20, 2010 at 7:58 am
Elutin,
I disagree. If you look closely, you will see that rows 1 & 2 for Table_A have the same File_Name value, similarly, rows 3, 4 & 5 (the clue is that this column is the Grouped column) have the same File_Name value. The output is immaterial.
As I stated in the original posting, the required function needs to output all rows where the File_Name = 1406857_20100517.xml (the 1st 2 rows) and then update their Load_Id to '1'.
After other functions are performed, I then need to output the rows where the File_Name = 1406859_20100517.xml (that's rows 3, 4 and 5) as shown in the above posting.
May 20, 2010 at 9:49 am
As I said before, it would be VERY helpfull if you provide the create table and data insert script together with the question.
Are you asking about:
required function needs to output all rows where the File_Name = 1406857_20100517.xml (the 1st 2 rows) and then update their Load_Id to '1'.
Ok. First of all SQL user define fuinction can not perform UPDATE, however it is possible by using stored proc:
1. See what I mean by having your create table and insert data scripts included in the question:
create table Table_A
(Id int, [File_Name] varchar(50), Load_Id bit)
insert into Table_A
select 1, '1406857_20100517.xml', 0
union select 2, '1406857_20100517.xml', 0
union select 4, '1406859_20100517.xml', 0
union select 5, '1406859_20100517.xml', 0
union select 6, '1406859_20100517.xml', 0
union select 7, '1117523_20100518.xml', 0
union select 8, '1117527_20100518.xml', 0
union select 9, '1117527_20100518.xml', 0
union select 10, '1117527_20100518.xml', 0
union select 11, '1252421_20100518.xml', 0
union select 14, '1252337_20100518.xml', 0
union select 15, '1252337_20100518.xml', 0
union select 16, '1515428_20100518.xml', 0
union select 17, '1515428_20100518.xml', 0
union select 18, '1531215_20100518.xml', 0
union select 19, '1531215_20100518.xml', 0
2. Stored proc:
CREATE PROC [dbo].[usp_Whatever]
(
@FileName varchar(50)
)
as
BEGIN
SELECT Id, [File_Name], Load_Id FROM Table_A WHERE [File_Name] = @FileName
UPDATE Table_A SET Load_Id=1 WHERE [File_Name] = @FileName
END
Now if you execute this proc as:
EXEC usp_Whatever '1406857_20100517.xml'
You will see that it will output
"all rows where the File_Name = 1406857_20100517.xml ..."
Now check the data in the Table_A. You will see that Load_Id for these records is set to '1'.
See, it does exactly what you've asked for. However I have a doubt that it is what you really want...
Cheers,
Me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply