append sp

Question

Post reply

append sp

sdhanpaul

Right there with Babe

Points: 787
More actions
November 26, 2012 at 4:38 pm

#267182

Hi,
not quite sure if this is info info...but i wanna convert this sp to an append sp.
with rows as (
select *, row_number() over (order by emppin, trxdate) as rownum
from rawtrx)
select *, rowsMinusOne.trxtime as trxtimeIN,
ISNULL(rows.trxtime,1) as trxtimeOUT
from rows as rowsMinusOne
left outer join rows
on rows.rownum = rowsMinusOne.rownum + 1 and rows.emppin = rowsMinusOne.emppin
Think you can help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

I'm not sure what you mean. That's not an sp and I'm unaware of the term append sp.

Could you clarify what you need?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sdhanpaul Right there with Babe Points: 787 More actions · Answer 2

ALTER PROCEDURE dbo.zz_sp_testing_inout

AS

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM rawtrx)

SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT

FROM rows AS rowsMinusOne LEFT OUTER JOIN

rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin = rowsMinusOne.emppin

Okay, does that look more like a stored procedure?

well this works exactly the way I want it to work, it returns the records...I want to add the records returned by this code above to a table.

is that possible?

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Yes, if the table already exists then add an INSERT INTO command below the CTE declaration above the SELECT.

If the table doesnt exist, add the INTO clause above the FROM.

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM rawtrx)

INSERT INTO atable (col1, col2,.................)

SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT

FROM rows AS rowsMinusOne LEFT OUTER JOIN

rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM rawtrx)

SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT

INTO atable

FROM rows AS rowsMinusOne LEFT OUTER JOIN

rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

Yes, if the table already exists then add an INSERT INTO command below the CTE declaration above the SELECT.

If the table doesnt exist, add the INTO clause above the FROM.

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM rawtrx)

INSERT INTO atable (col1, col2,.................)

SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT

FROM rows AS rowsMinusOne LEFT OUTER JOIN

rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin

WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum

FROM rawtrx)

SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT

INTO atable

FROM rows AS rowsMinusOne LEFT OUTER JOIN

rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin

sdhanpaul Right there with Babe Points: 787 More actions · Answer 5

ah! yes, i did try this ...i got an error but i know what the reason for the error is so it will work. Thanks.