One way to archive data in a single step is to use the OUTPUT clause of the DELETE statement. The procedure takes @MaxRows as a parameter, stating the maximum...

Here is an alternate version using a Tally table.

DECLARE@SelectedDateDATE = '2012-02-01' --GETDATE()

DECLARE@DesiredDayTINYINT = 5

SELECTTOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDay

FROM(

SELECTDATEADD(DAY, N, @SelectedDate) AS NextDay

FROMTally

WHEREN <= 14

) x

WHEREDATEPART(WEEKDAY, NextDay) = @DesiredDay

ORDER...

You can also open the Excel document directly from SQL Server and SELECT from it just like using a table.

From: http://support.microsoft.com/kb/321686

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT *...

Sean Lange (1/18/2012)

---

How can you get a value from an insert that isn't in the inserted table? If you insert it, it is in the inserted table. The inserted table...

We are not trying to do an UPDATE or a DELETE, so using the OUTPUT clause with those statements does not help.

We want to get the value of a column...

In most professions, the "tools" do not become obsolete in two years. The screwdriver I made 25 years ago still works in every flathead screw in my house. ...

I'm going to suggest a different strategy because of the complexity of the need. You need to bring forward each row from the prior month, meaning that if a...

The OUTPUT clause is going to work only with the MERGE statement.

CREATE-- DROP -- TRUNCATE

TABLECompOrgReference

(

[CompanyID]INTEGERNOT NULL,

[Organization_ID]INTEGERNOT NULL

)

MERGEOrganizationRecords u

USING(

SELECTr.Organization_ID, c.CompanyID, c.CompanyName

FROMCompanyRecords c

LEFT JOIN CompOrgReference r

ONr.CompanyID = c.CompanyID

WHEREr.CompanyID IS NULL

) x

ONx.Organization_ID =...

Two questions:

1 - Are you saying that DerivedFact is a view, not a table?

2 - Why is CMP1001 copied from two months prior (2011/11) rather than one month prior (2011/12)?...

Joe seems to...

Here is another option.

-- Samle working table

CREATE-- DROP

TABLEOrdersMissing

(

OrderNumINTEGERNOT NULL,

MissingValuesINTEGERNOT NULL

)

-- Sample data

INSERT

INTOOrdersMissing

VALUES(1234, 3),

(1235, 23),

(1783, 5)

-- Produce one record for each item listed as missing.

SELECTm.OrderNum,

Tally.N,

'Other needed columns here...'

FROMOrdersMissing m

JOIN(

SELECTROW_NUMBER() OVER (ORDER...

Well, a cursor MAY not be the way to go; it depends on the complexity of the function. If it is a large or complex function, you may see...

A cursor is not the way to go.

UPDATECustomer

SETPercentage = i.Percentage

FROMCustomer u

JOIN(

SELECTCustomerID, dbo.GetPercentage(Value) AS Percentage

FROMInserted

) i

ONi.CustomerId = u.CustomerId

The "i" subquery gets one row per record in the Inserted table, with the...

CREATE-- DROP

TABLEOriginalFact

(

AccountVARCHAR(10)NOT NULL,

OrganizationVARCHAR(10)NOT NULL,

[Year]INTEGERNOT NULL,

PeriodSMALLINTNOT NULL,

AmountINTEGERNOT NULL

)

CREATE-- DROP

TABLEDerivedFact

(

AccountVARCHAR(10)NOT NULL,

OrganizationVARCHAR(10)NOT NULL,

[Year]INTEGERNOT NULL,

PeriodSMALLINTNOT NULL,

AmountINTEGERNOT NULL,

TypeCodeCHAR(1)NOT NULLCHECK(TypeCode IN ('C', 'P'))

)

-- Sample data for the OriginalFact table.

INSERT

INTOOriginalFact

(Account, Organization, [Year], Period, Amount)

SELECTAccount, Organization, [Year], Period,...

It appears that your first line of data has only a single "/":

MYSITE_Lion/EEStaticHeaderIncludes

Searching for the second "/" will return an error because the location of the starting position for the...