Multiple results into single insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple results into single insert

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-15 : 06:51:27

I have an address table which has been checked by an outside agency for accuracy.
So, I have two tables:

originaladdress
----------------
clientid int,
Forename varchar(15),
Surname varchar(15),
Address1 varchar(30),
Address2 varchar(30),
Address3 varchar(30),
Address4 varchar(30),
Address5 varchar(30),
PostCode varchar(8)

checkedaddress
--------------
clientid int,
ForenameFlag char(1),
SurnameFlag char(1),
AddressFlag char(1),
NewForename varchar(15),
NewSurname varchar(15),
NewAddress1 varchar(30),
NewAddress2 varchar(30),
NewAddress3 varchar(30),
NewAddress4 varchar(30),
NewAddress5 varchar(30),
NewPostCode varchar(8)

I wish to insert into a third table

DataNotes
---------
clientid int,
notes text

The ForenameFlag, SurnameFlag and AddressFlag columns can contain either 'Y' or some other character.
If these flag columns contain a character other than 'Y' then the notes portion of the entry will contain
an entry like to indicate the difference between the existing data in table originaladdress and the new data in checkedaddress like so:

'Forename differs: Current record = Bill. New record = David + char(13) + char(10)
Surname differs: Current record = Smith. New record = Brown + char(13) + char(10)
Address differs: Current record = xxxxxxxxxxxxxx. New record = yyyyyyyyyyyyyyy'

The three lines shown above are all in the SAME record entry.

How can I do this please?

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 07:39:04

[code]INSERT INTO DataNotes(clientid, notes)
SELECT [clientid],
[notes] = LEFT([notes], DATALENGTH([notes])-2) -- Chop off trailing CR/LF
FROM
(
SELECT [clientid] = O.clientid,
[notes] = CASE WHEN ForenameFlag = 'Y' THEN ''
ELSE 'Forename differs: Current record = ' + COALESCE(O.Forename, '[NULL]')
+ '. New record = ' + COALESCE(N.Forename, '[NULL]') + char(13) + char(10)
END
+ CASE WHEN SurnameFlag = 'Y' THEN ''
ELSE ...
FROM originaladdress AS O
JOIN checkedaddress AS N
ON N.clientid = O.clientid
) AS X
WHERE X.[notes] <> ''
[/code]

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 07:41:39

"DataNotes
---------
clientid int,
notes text"

[notes] needs to be VARCHAR(MAX) - and not TEXT datatype

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 07:45:57

and the reason is stated in BOL

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 07:52:52

Yeah ... actually I was thinking that if the concatenation is more than 8000 characters I didn't think it would store in a TEXT - but maybe it will be created in a VARCHAR(MAX), as an intermediate variable, and then CAST to a TEXT ?

So maybe it will work ... until TEXT is deprecated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 07:56:21

quote:
Originally posted by Kristen

Yeah ... actually I was thinking that if the concatenation is more than 8000 characters I didn't think it would store in a TEXT - but maybe it will be created in a VARCHAR(MAX), as an intermediate variable, and then CAST to a TEXT ?

So maybe it will work ... until TEXT is deprecated

We can't assume until it is well documented

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-15 : 08:36:01

Many thanks Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources