I have two questions:

1. According to your #Sample1 data, the ShipReturnType for this expected output row should be 2 and not 7 (please confirm).

001700001829830—2010-11-06---2011-12-08------7--------43

2. I don't understand how you're matching...

Here's another approach directly from the data you provided (normalization issues aside):

declare @MappedField varchar(200) = 'Company Name'

;WITH CTE AS (

SELECT ID, a,...

Wow Chris! That's pretty...

ScottPletcher (6/21/2012)

---

Since there's only three dimensions being tested, I wonder if it's easier to just add computed columns to the base table and do all the calcs inline in one...

Siten0308 (6/21/2012)

---

sorry all, my fault, yes i definitly want to say dwain solved the problem, when i did it, i was doing it on the same table X(, again my...

Ooooooh! I like this.

FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

Much cleaner that what I've been using!

BTW. Your demonstration didn't quite hit the mark about the ampersand because the string was...

Try this:

-- Note: Cannot do this with a table variable

CREATE TABLE #rec ([client] varchar(50), [type of transaction] varchar(50), [amount] varchar(50))

insert into #rec

select 'acme' as [client], 'ammendment' as [type of transaction],'100'...

An outstanding exercise in futility!

DECLARE @t TABLE (col1 VARCHAR(2), col2 VARCHAR(2), col3 VARCHAR(2))

INSERT INTO @t

SELECT 'a','aa','z'

UNION ALL SELECT 'b','ab','z'

UNION ALL SELECT 'c','ac','z'

UNION ALL SELECT 'm','am','y'

UNION ALL SELECT 'n','bn','y'

;WITH t AS...

Here's another way but it's no prettier and no easier to maintain.

SELECT Name

,(SELECT CASE WHEN RED = 1 THEN 'RED' ELSE '' END

...

You can try it like this:

DECLARE @TableA TABLE (name VARCHAR(50), dob DATETIME, dos DATETIME, ssn VARCHAR(9)

,ssn_match CHAR(1), dob_match CHAR(1), dos_match CHAR(1))

DECLARE @TableB TABLE (name VARCHAR(50), dob DATETIME, ssn VARCHAR(9))

INSERT INTO...

Sounds like you want to:

1. Create an SSIS package to import your Excel on the schedule.

2. You can UPDATE existing rows and INSERT new rows (colloquially called an UpSert) using...

The smartest way is also usually the fastest way.

In this case, that would be picking it up out of the systables.

I was just having a bit of fun. 🙂

How about something just a tad more interesting?

CREATE TABLE #sss

([Account] [varchar](50) NOT NULL,

[datetrans] [datetime] NOT NULL,

[uniqueID] [int] NOT NULL,

[TranAmount] [numeric](18, 2) NOT NULL,

[TranBal] [numeric](18, 2) NOT NULL

) ON [PRIMARY]

insert into...

Yeah, but can you do it in less than 90 keystrokes?

DECLARE @CR INT = 0

UPDATE...

joe-871382 (6/20/2012)

---

Hi,

I know I am late to the conversation, but I can't seem to figure out how to actually create the file on a drive somwhere that I can...