Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

append sp Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
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?
Post #1388894
Posted Monday, November 26, 2012 5:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:42 PM
Points: 3,576, Visits: 8,026
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388901
Posted Tuesday, November 27, 2012 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
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?
Post #1389072
Posted Tuesday, November 27, 2012 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1389082
Posted Tuesday, November 27, 2012 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1389085
Posted Tuesday, November 27, 2012 6:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:08 PM
Points: 43, Visits: 566
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.
Post #1389479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse