Re: SP to record inputs plus random records from 2nd table - Database Discussion Boards

Thanks Luc, Yes, it can be solved using pivot tables or the decode function but my problem is far more complicated to be solved by either of them alone!
Thanks for your time mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Absolutely. That's why I said, I wouldn't start from there. With people being forced to use designs like that, it's little wonder that some of the questions asked in the forums seem to be a bit daft.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

The answer is pretty simple

SELECT (SUM(JAN) + SUM(FEB)) LAST_2_MONTHS_RESULT FROM TBLMONTH

This query will work even for if the column values are null.

e.g.

JAN     FEB     MARCH   APRIL
---     ---     -----   -----
10	10	10	10
NULL	20	11	12
20	40	1	1
30	NULL	5	6
NULL	NULL	NULL	NULL

The answer for such a case is

LAST_2_MONTHS_RESULT
-------------------
130

Hope it helps
Smile | :)

Niladri Biswas

Thanks mate!
That was quite an answer but still it doesnt answer my question, all you did is add the previous two months literally, anyways, I've already worked that out using a stupid syntax from C# that has nothing to do with SQL. Thanks anyways mate!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Hi,

Does this solution solved your problem. If so, please close the issue by marking the

question's answer as a "good answer".

If not then please response so that we can come to know where is the precise problem and

will help with all posible efforts.

Because if you get the right solution and is not marking it as good answer, then it is not

getting close and is listing in the Unanswered questions section , so it is becoming

difficult to filter out which are the true unresolved issues.

Please co-operate.

Niladri Biswas

Thanks again, but I already said your earlier replay doesnt answer my question and it would be misleading to mark it as a good answer.

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Hi,

I'm in need of a stored procedure which takes 5 input values and then stores them in 'Table A'. This is simple enough.

However i also need to get data from a 2nd table 'Table B', return 5 random records(1 column only) and then input these records into 'Table A' along with the other data mentioned above. (Making a single record / row of 10 bits of information)

Hopefully this makes sense.

I know how to create the select statement for the above, but how do i then capture this data within the stored procedure and use it in the insert statement?

Thanks! D'Oh! | :doh:

Your question is not very clear, because

a) You never specified if the data in TableA has been inserted already and

then you want to update the 6th column of TableA [ As you

specified that the first 5 fields of TableA will have values supplied from

the Stored Proc which indicates that the 6th column will have values from

TableB ] with the values from TableB.

b) The Table Structure of both the tables are absent.

c) What will be the ultimate output [ Any rough sketch will make the

picture more clear]

d) Whether Table2's column will have predefined value or not. If not

whether the values needs to be inserted at runtime in TableB and then it

has to be fetched and next to be inserted in TableA?

e) Any dependency is lying between the two tables(e.g. Referential

Integrity Constraint)

However, I am assuming that TableA has the following structure

TableA

Col1 Col2 Col3 Col4 Col5 Col6

All are Varchar(50) types.

N.B.~ Col6 will have the value from TableB

TableB

RandomCol

Again Varchar(50)

And my Stored Proc is as under:

ALTER PROCEDURE InsertRecordsIntoTableA
	-- Add the parameters for the stored procedure here
	(
		@VarCol1 AS VARCHAR(50),
		@VarCol2 AS VARCHAR(50),
		@VarCol3 AS VARCHAR(50),
		@VarCol4 AS VARCHAR(50),
		@VarCol5 AS VARCHAR(50)
	)
AS
BEGIN
	
	-- VARIABLE DECLARATION
	DECLARE @RANDOMCOLUMNVALUES VARCHAR(50)

   -- STEP 1: DECLARE A CURSOR
	DECLARE MYRANDOMCURSOR CURSOR FOR

		SELECT    RANDOMCOL
		FROM      TABLEB

	-- STEP 2: OPEN THE CURSOR
	OPEN MYRANDOMCURSOR 

	FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES

	-- STEP 3: START THE LOGIC

	WHILE @@Fetch_Status = 0

	BEGIN

		-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE A

			INSERT INTO TABLEA(COL1,COL2,COL3,COL4,COL5,COL6)

			VALUES(@VarCol1,@VarCol2,@VarCol3,@VarCol4,@VarCol5,@RANDOMCOLUMNVALUES)
		
	-- STEP 5: GET THE NEXT RECORD
	FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES           

   END

	--STEP 6: CLOSE THE CURSOR
	CLOSE MYRANDOMCURSOR

	--STEP 6: DEALLOCATE THE CURSOR
	DEALLOCATE MYRANDOMCURSOR
	
END

Hope this answers you question.

If not, please tell your problem precisely with necessary

description

It can even be solved without using CURSOR and with a while loop

Please be specific about your question so that others can understand

properly.
Smile | :)

Niladri Biswas

Good Day all i have the Following Query

select distinct convert(varchar(10), (convert(smalldatetime, c.descr)), 120) [Date], tt.Sess [Session], m.descr [Subject], ctyp.Abrev [PaperType], ct.Number [PaperNumber], a.GrpName [Campus], a.Duration, CASE WHEN MAV.STUDENTS IS NULL THEN a.Students ELSE MAV.STUDENTS END [Students], v.descr [Venue], v.Capacity, s.descr [Staff] 
             -- into #Timetable 
              from sol_actv_time tt 
              inner join tbl_clmn c on c.id=tt.Dy 
              inner join tbl_actv a on a.id = tt.actv 
              inner join tbl_cntc ct on ct.id = a.cntcid 
              inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp 
              inner join tbl_modl m on m.id = ct.modlid 
              inner join tbl_term t on t.id = a.term 
              left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id 
              left outer join mtm_actv_venue mav on mav.venue=ttVenu.Venu and mav.actv=a.id 
              left outer join tbl_venue v on v.id = ttVenu.Venu 
              left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id 
              left outer join tbl_staff s on s.id = ttStaff.Staff 
              select activity.* from #Timetable activity for xml auto, elements

I get the Following Error

Msg 295, Level 16, State 3, Line 2
Conversion failed when converting character string to smalldatetime data type.

Thanks

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

I don't have sql server in front of me so I can't test unfortunately.

What is the data type of c.descr?

Its a String but its in a Date Format.

Thanks i have resolved this by accepting the data as data from my presentation layer.

Thanks Thumbs Up | :thumbsup:

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Hi All

I'm not a SQL guy, so I'm stuggling a bit. I hope you guys can help me.

I have an SSIS package which copies tables from one server to another. However, the source tables will, from time to time, get new fields added to it. My understanding of SSIS is that when you are copying a table, you either need the destination table to already exist, or you need to create the table via a predetermined table build script. Is there a way of copying the source table to the destination regardless of what fields are in it?
(we could just change the SSIS package to reflect the changes, but it can take over a week for our DBA's to swap it out for us)

Any help would be greatly appreciated. Smile | :)

Thanks

oooo, the Jedi's will feel this one....

Paul Unsworth wrote:
s there a way of copying the source table to the destination regardless of what fields are in it

Not to my knowledge and your DBAs will already have told you this Big Grin | :-D

It is, via a LOT of design commitment create a package that reads the source structure and changes the destination structure but I would take a suit of armour when you approach the DBAs, they will first deny it is possible, then point out it is unreasonable and then quit if you insist. There is nothing a DBA hates more than someone screwing with their data structure.

I have done this when the business rule was that columns were only ever ADDED and never MOVED, but it was a right, royal PITA.

Never underestimate the power of human stupidity
RAH

Haha. Fairy nuff. I suppose it makes sense. Although not quite what I want.

I suppose I'm just a control freak... Laugh | :laugh:

It's just our DBA's are as quick as geriatric slugs. Countless forms to fill in, just so that they can take a week to think about it, then either say no, hope that the requestee forgets about it, or say "yes. It will be operational in 2 weeks...". Can be a little frustrating.

I hope not all DBA's are like this. I may look into DB admin myself...

Thanks for you reply. Big Grin | :-D

oooo, the Jedi's will feel this one....

Paul Unsworth wrote:
I hope not all DBA's are like this

A crappy DBA is a developers nightmare, however the timeframe you find so irritating may be due to all the change management crap the DBA has to go through to effect an "Enterprise" level change. I work on departmental systems and do not need to conform to these rules thankfully.

This particular change, while challenging, goes against all their training so I would think you have buckleys of getting it done Laugh | :laugh:

Never underestimate the power of human stupidity
RAH

i am trying to insert data or save data from language other than english in an access database.but it is giving me some error and not allowing me to save the same.

if anyone can find a solution please let me know as soon as possible.

Thanks in advance

- Can you show the code to insert/update?
- What error did you get?

I are troll Smile | :)

Hi Experts
I Have a database having only one table and this table has 12 Columns all Columns
have data type varchar(Max).Now i fill Grid By Searching Record on Particular Field
After The Result Come To me in grid i further filter the record in the incoming
result set that is come in grid.this filter process may me n -times so how i can
save the incoming result and filter them.the data inside the table is near about 100000 (1Lakhs) records.The Record search is also faster.
Currently i am using the SQL Server 2005 and C#.Net(2.0 Framework).
How i Make It Faster and Filter the record in n-pass. so i have not make round trip
on sql server every time when i search the record . i have display the record not to
save or update it in Database. i am not change my data type beacuse it store heavy
Data inside the single fields
Thanku
Dinesh Sharma

Instead of filtering your data in the grid, why not use a stored procedure (with parameters) to just return the records you are interested in?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile Blog
Booger Mobile - Official Camp Quality esCarpade Entrant Page

Thanks
But Accutly Query Creating At Runtime User Can Search The Record in Any Fields.
Ok I am Agree With u i am making SP but the data come one time i filter using SP
but the next time i have filter data from the in coming result set not from whole database. i mean to say that where we can store result temp. so i can filter further
from in coming result accutly it a search engine software,so where we can store it.

Assuming you have some sort of applciation you could store the data from your first search locally and then filter that - in .net a datatable would be the thing to use, otherwise maybe some sort of collection?

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

pls send me example if possible

Without knowing anything about your application, including what language it is in and what type of application it is, how can I? Anyway, 2 minutes on google should give you plenty of examples, its a common enough thing to do.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

i have mention my tech. what i am using for you'r knowledge i am using sql server v2005
and VS 2005 .

OK, I had forgotten. Anyway, just google for datatable, dataadaptor and filter and you should be sorted, ther are hundreds of examples available.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP