October 4, 2005 at 3:16 pm
Hi,
I am trying to write a routine that will import many records through a cursor. The data source comes from a table imported with a simple import wizard.. I need to be able to select only records that has an x in a specific column that I pass in parameter.. Can the select statement in a cursor use a paramenter?? Right now, when I run it it imports nothing (I've tested my sp InsertItemType and it works.. and if I change @park for the specific column name, it works..
--------- here is my code -----------------
create procedure ImportItemType (@park char(2))
as
Declare CursorItemTypeImport insensitive cursor for
select ItemType from import where @park = 'x'
for read only
Open CursorItemTypeImport
Declare @ItemType varchar(20), @ParkID char(2)
fetch CursorItemTypeImport into @ItemType
while @@fetch_status = 0
BEGIN
exec InsertItemType @ItemType, @Park
fetch CursorItemTypeImport into @ItemType
END
close CursorItemTypeImport
Deallocate CursorItemTypeImport
GO
Thanks in advance!
October 5, 2005 at 2:40 am
I believe you would have to build a dynamic SQL statement or define the table column to be used in the Where. Currently SQL would parse the Where clause as...
Where 'a' = 'x';
when @park = 'a'
Could you try something like:
WHERE
(@park = 'column1' AND [column1] = 'x')
OR
(@park = 'column2' AND [column2] = 'x')
OR
(@park = 'column3' AND [column3] = 'x')
HTH,
-Mike Gercevich
October 5, 2005 at 7:12 am
Why can't you just do a single insert statement??
October 5, 2005 at 8:20 am
Can you explain?
Thanks
October 5, 2005 at 8:30 am
Insert into dbo.ItemTypes (Description)
select ItemType from dbo.import where SomeCol = 'x'
October 5, 2005 at 8:37 am
Can I use an insert select if I need to transform data
Here is my Insert statement:
create procedure InsertItemType (@itemtype varchar(20), @parkID char(2)) as
declare @NewItemType varchar(20)
set @NewItemType = @ParkID + @ItemType
insert into itemtypecopy(itemtype, parkid, interestcode, recordstatus, replicate_id, lastupdatedate, lastupdatetime)
values (@NewItemType, @parkID, 'None', 'A', 1, CONVERT (char(10), getdate(),111), CONVERT (char(8), getdate(),114))
Thanks for your replies... it is appreciated
October 5, 2005 at 8:50 am
Where do you get the ParkID value?
Why do you split the date and time?
insert into itemtypecopy(itemtype, parkid, interestcode, recordstatus, replicate_id, lastupdatedate, lastupdatetime)
select ItemType + ParkID, ParkID, 'None', 'A', 1, CONVERT (char(10), getdate(),111), CONVERT (char(8), getdate(),114) from dbo.import where SomeCol = 'x'
October 5, 2005 at 12:20 pm
Thanks for all your replies..
I'm importing data in an application and the time and date resides in 2 different columns...
October 5, 2005 at 12:21 pm
I c... too bad you can't do anything about it.
October 5, 2005 at 12:51 pm
Hi,
It seems I have a problems with strings and quotes.. I need to create this statement as an Dynamics SQL and I cannot get the quotes right
insert into itemcode1(itemtype, itemcode1, interestcode, recordstatus, replicate_id, lastupdatedate, lastupdatetime)
select distinct @ParkID + Itemtype, itemcode1, 'None', 'A',1, CONVERT (char(10), getdate(),111), CONVERT (char(8), getdate(),114)
from import where @ParkID ='x'
I did not read all the papers on the pitfalls of Dynamics SQL, should I do this another way?? I started with writing a cursor but it is way easier this way.
Thanks again
October 5, 2005 at 12:54 pm
There's just no need for dynamic sql here... what's the problem??
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply