would like to avoid cursor, please help

[limester]

Hi,

I need to query a database for a recordset and insert this into another database row-by-row.

For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MM:SS.0000 so for each inserted record the last decimal point would increment by 1.

Can I do this without a cursor ?

Any additional info I will provide gladly.

Thanks!

 

[bborissov]

What type is Time filed? DateTime, varchar or else?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Here one example:

DECLARE @Test TABLE (Fld1 varchar(20))
INSERT INTO @Test VALUES ('12:12:44.0000')
INSERT INTO @Test VALUES ('12:13:44.0000')
INSERT INTO @Test VALUES ('12:14:44.0000')
INSERT INTO @Test VALUES ('12:15:44.0000')
INSERT INTO @Test VALUES ('12:16:44.0000')
INSERT INTO @Test VALUES ('12:17:44.0000')
INSERT INTO @Test VALUES ('12:18:44.0000')
DECLARE @Cntr int
SET @cntr = 0

UPDATE @Test SET @cntr = @cntr + 1,
                 Fld1  = LEFT(Fld1,9)+RIGHT('0000'+CAST(@cntr as varchar(4)),4)
SELECT * from @Test

So generaly you could use:

--- Get RecordSet Into Temporary table
SELECT *
       INTO #Temp
       FROM ....
       WHERE ...

DECLARE @Cntr int
SET @Cntr = 0
UPDATE #Temp SET TimeField = ..... (see example above)
                 @Cntr = @Cntr + 1
INSERT INTO OtherTable
SELECT * FROM #Temp
DROP TABLE #Temp

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[limester]

Hello Borislav,

Thank you for your reply. I think the best method for what I need to do is to use a cursor.

It is not a heavily loaded Dbase and I could create this cursor as a stored procedure and have it run nightly.

I can use some of what you have provided thus far, but I still require some help. I have a better understanding of what I need to accomplish from my initial post.

I want to create a cursor that will do the following:

- do a select query
- use the columns in the query result to assign variables
- insert these results one by one into an existing table

for example:

- query
SELECT col1, col2, col3
FROM table1
WHERE col4 = SELECT CONVERT(char(12), getdate(), 102)

- assign variables
@col1
@col2
@col3

- insert
INSERT INTO table2 VALUES (@col1, @col2, @col3, 'employee')
INSERT INTO table2 VALUES (@col1, @col2, @col3, 'department')
INSERT INTO table2 VALUES (@col1, @col2, @col3, 'ID')

- then fetch the next result from initial query and do the same insert. Keep doing this until there are no results left.

Any help is greatly appreciated!!

Thanks!

 

[gmmastros]

So... if Col4 shows today's date, then you want to insert the records from table 1 in to table 2? You don't need a cursor for this. Cursors are notoriously slow and probably over utilized by most sql programmers.

From the looks of it, you want 3 records in table2 for each record in table 1 (where the col4 column is today's date). Assuming I understand correctly, I suggest you try the following query. Please, make a backup of the database before doing this to make sure it doesn't cause any problems.

Insert 
Into   Table2(Col1, Col2, Col3)
Select Col1, Col2, Col3, 'employee'
From   Table1
Where  Col4 = Convert(Char(12), GetDate(), 102)

Insert 
Into   Table2(Col1, Col2, Col3)
Select Col1, Col2, Col3, 'department'
From   Table1
Where  Col4 = Convert(Char(12), GetDate(), 102)

Insert 
Into   Table2(Col1, Col2, Col3)
Select Col1, Col2, Col3, 'ID'
From   Table1
Where  Col4 = Convert(Char(12), GetDate(), 102)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[limester]

Hi gmmastros,

Thank you for your reply.

I am sorry if I misunderstood your post, or more than likely I have not explained correctly, but this does not look like it will work for me.

Table1 will have an infinite number of rows, but for a single day there are approximately 500 rows.

Each orw in my initial query will have a different set of data in its columns. How will I distinguish between which row is 1 and which row is 50? Will your post above not insert the same row everytime?

Thanks!

 

[limester]

Sorry, I should also have stated that each set of inserts end up as a single transaction. A service will fetch the data for each transaction.

What I need to accomplish in the end is doing each set of inserts and applying a unique time-stamp (so that the service can distinguish), but for now I am just trying to work out how to accomplish the inital task.

Thanks!