March 2, 2015 at 5:14 am
Hi there,
I've got an audit table which by using DelimitedSplit8K I am able to show the before and after value of a particular field.
In this example I am showing the changes I have made to the job title of a particular person.
selectCONCAT(o1.Item,' ',o2.Item) 'Staff Name'
,au.date_of_change AS 'ChangeDateTimeStart'
,au.date_of_change AS 'ChangeDateTimeEnd'
,au.user_name AS 'User'
,bi.Item 'Before'
,ai.Item 'After'
from [co_audit_trail] au
cross apply mhsInsight.dbo.DelimitedSplit8K(bi,'ª') bi
cross apply mhsInsight.dbo.DelimitedSplit8K(ai,'ª') ai
cross apply (select Item FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 15) o --Job Title--
cross apply (select Item FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 17) o1 --Forename--
cross apply (select Item FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 5) o2 --Surname--
where au.modified_table = 'ih_officer'
and bi.ItemNumber = ai.ItemNumber
and bi.ItemNumber = '29'
and CONCAT(o1.Item,' ',o2.Item) = 'Ryan Keast'
ORDER BYau.date_of_change
So all that is showing me is the changes to the Officer of Ryan Keast and that member of staff's Job Title (which is ItemNumber 29)
This gives me the results in audit.jpg. Which shows two changes.
I now have my main code here - which just shows how many entries on the system I have made
SELECT crm.person_ref AS 'PersonRef'
,crm.open_date + crm.open_time AS 'LoggedDateTime'
FROM dbo.[ih_cml_log_entry] AS crm
LEFT OUTER JOIN
[dbo].[co_user_defaults] de
ONcrm.[open_user] = de.[user_name]
LEFT OUTER JOIN
[dbo].[ih_officer] o
ON de.officer_code = o.officer_code
WHERE crm.open_date >= CONVERT(DATETIME, '2014-04-01 00:00:00', 102)
and crm.log_type <> 'SYS'
and CONCAT(o.forename,' ',o.surname) like 'Ryan%'
The results for this is attached as entries.jpg. There are three entries.
So what I'm trying to do is to pull back the job title of that person when they entered the entry on the system.
To end up with something like below - With the final column being the job title at the point of time that the entry was made.
Entry 1 - 009545 - 2015-02-27 15:56:17.000 - ICT Systems Analyst
Entry 2 - 009545 - 2015-02-27 16:01:06.000 - ICT Systems Analyst1
Entry 3 - 009545 - 2015-02-27 16:02:31.000 - ICT Systems Analyst2
I don't really know how to achieve this?
Thank you
March 2, 2015 at 7:22 am
Can you post a set of data for table from [co_audit_trail] au please? DDL and DML. It will make this exercise much easier for folks to work on if they can see your data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 2, 2015 at 8:01 am
Sorry - It won't let me do this -
To create the table -
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[co_audit_trail2](
[user_name] [varchar](60) NULL,
[date_of_change] [datetime] NULL,
[time_of_change] [varchar](16) NULL,
[bi] [varchar](8000) NULL,
[ai] [varchar](8000) NULL,
[modified_table] [varchar](60) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
However when I run the part to create the data -
SELECT 'SELECT '
+ QUOTENAME(user_name,'''')+'*'
+ QUOTENAME(date_of_change,'''')+'*'
+ QUOTENAME(time_of_change,'''')+'*'
+ QUOTENAME(bi,'''')+'*'
+ QUOTENAME(ai,'''')+'*'
+ QUOTENAME(modified_table,'''')
+ ' UNION ALL'
FROM [dbo].[co_audit_trail] au
where au.modified_table = 'ih_officer'
The 6 results I would expect just come back as below -
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
NULL
NULL
NULL
NULL
NULL
(6 row(s) affected)
March 2, 2015 at 9:18 am
There are NULLs in your data. Try this:
SELECT 'SELECT '
+ ISNULL(QUOTENAME(user_name,''''),'NULL') + ','
+ ISNULL(QUOTENAME(date_of_change,''''),'NULL') + ','
+ ISNULL(QUOTENAME(time_of_change,''''),'NULL') + ','
+ ISNULL(QUOTENAME(bi,''''),'NULL') + ','
+ ISNULL(QUOTENAME(ai,''''),'NULL') + ','
+ ISNULL(QUOTENAME(modified_table,''''),'NULL')
+ ' UNION ALL'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 2, 2015 at 9:20 am
Try
SELECT 'SELECT '
+ COALESCE(QUOTENAME(user_name,''''),NULL)+','
+ COALESCE(QUOTENAME(date_of_change,''''),NULL)+','
+ COALESCE(QUOTENAME(time_of_change,''''),NULL)+','
+ COALESCE(QUOTENAME(bi,''''),NULL)+','
+ COALESCE(QUOTENAME(ai,''''),NULL)+','
+ COALESCE(QUOTENAME(modified_table,''''),NULL)
+ ' UNION ALL'
FROM [dbo].[co_audit_trail] au
where au.modified_table = 'ih_officer'
March 2, 2015 at 9:30 am
Nevyn (3/2/2015)
Try
SELECT 'SELECT '
+ COALESCE(QUOTENAME(user_name,''''),NULL)+','
+ COALESCE(QUOTENAME(date_of_change,''''),NULL)+','
+ COALESCE(QUOTENAME(time_of_change,''''),NULL)+','
+ COALESCE(QUOTENAME(bi,''''),NULL)+','
+ COALESCE(QUOTENAME(ai,''''),NULL)+','
+ COALESCE(QUOTENAME(modified_table,''''),NULL)
+ ' UNION ALL'
FROM [dbo].[co_audit_trail] au
where au.modified_table = 'ih_officer'
That is exactly the same as the original, as any NULL in the string will simply NULL the entire string.
Eg 'Hello' +','+ NULL + ','+'World' will result in a NULL output for the entire string.
ChrisM's solution will negate that particular issue.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 2, 2015 at 9:58 am
yep, my bad, forgot the quotes.
March 4, 2015 at 8:14 am
HI, Thanks for your replies.
There shouldn't be any Null values.
At the bottom of this post - it shows two result sets - one using the code to generate you data -
SELECT 'SELECT '
+ ISNULL(QUOTENAME(user_name,''''),'NULL') + '*'
+ ISNULL(QUOTENAME(date_of_change,''''),'NULL') + '*'
+ ISNULL(QUOTENAME(time_of_change,''''),'NULL') + '*'
+ ISNULL(QUOTENAME(bi,''''),'NULL') + '*'
+ ISNULL(QUOTENAME(ai,''''),'NULL') + '*'
+ ISNULL(QUOTENAME(modified_table,''''),'NULL')
+ ' UNION ALL'
FROM [dbo].[co_audit_trail] au
where au.modified_table = 'ih_officer'
The bottom shows me a simple select statement of the same as the above - you will see that the bi and ai - has data in them - why then do they come back as NULL?
SELECT *
FROM [dbo].[co_audit_trail]
where modified_table = 'ih_officer'
user_name date_of_change time_of_change bi ai modified_table
------------------------------------------------------------ ----------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
ryank 2015-02-27 16:00:00.000 16:00:43:407 NULL NULL ih_officer
ryank 2015-02-27 16:02:00.000 16:02:15:227 NULL NULL ih_officer
joannab 2015-02-27 16:07:00.000 16:07:12:003 NULL NULL ih_officer
ryank 2015-03-02 09:35:00.000 09:35:57:170 NULL NULL ih_officer
ryank 2015-02-25 09:08:00.000 09:07:58:913 NULL NULL ih_officer
adamb 2015-02-25 13:24:00.000 13:24:16:993 NULL NULL ih_officer
adamb 2015-02-25 13:24:00.000 13:24:17:853 NULL ih_officer
(7 row(s) affected)
seq_no create_complete user_name db_event date_of_change time_of_change bi ai table_recid modified_table archive_date archive_time modified_db
-------------------- --------------- ------------------------------------------------------------ ---------------- ----------------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ----------------------- ---------------- ------------------------------------------------------------
808217 1 ryank WRITE 2015-02-25 09:08:00.180 09:07:58:913 01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@mhs.org.ukª0ª0ª0ª ª0ª ª ª ª ªCustomer Service Advisorª0ª0ª0ª0.00ª ª ªª444ª444ª0ªª ªª ª0ª 01ªRC1ªMrªRªCottissªª4231ª0ª0ª0ª0ª1ª0ª0ªAsset Managementª0ªRickyªªricky.cottiss@mhs.org.ukª0ª0ª0ªª0ªª ª ª ªPolicy & Data Co-Ordinatorª0ª0ª0ª0.00ª ª ªª444ª444ª0ª25/02/2015ª ªª ª0ª 0 ih_officer NULL ih
809558 1 adamb WRITE 2015-02-25 13:24:17.853 13:24:16:993 01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª 01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªª744ª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª 0 ih_officer NULL ih
809559 1 adamb CREATE 2015-02-25 13:24:18.273 13:24:17:853 01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª 0 ih_officer NULL ih
813986 1 ryank WRITE 2015-02-27 16:00:44.417 16:00:43:407 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª19/08/2014ªJB7ª12/08/2014ª14:37:18ª0ª 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª 0 ih_officer NULL ih
813987 1 ryank WRITE 2015-02-27 16:02:16.230 16:02:15:227 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª 0 ih_officer NULL ih
813998 1 joannab WRITE 2015-02-27 16:07:12.837 16:07:12:003 01ªJD7ªMrsªJªDobinsonª01634 83194ªª0ª0ª1ª0ª1ª0ª0ªFoyersª0ªJulieªªjulie.dobinson@mhs.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ªªAB6ª23/12/2014ª11:28:25ª0ª 01ªJD7ªMrsªJªDobinsonª01634 83194ªª1ª0ª1ª0ª1ª1ª0ªFoyersª0ªJulieªªjulie.dobinson@mhs.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ª27/02/2015ªAB6ª23/12/2014ª11:28:25ª0ª 0 ih_officer NULL ih
854568 1 ryank WRITE 2015-03-02 09:35:57.643 09:35:57:170 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª 01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª02/03/2015ªJB7ª12/08/2014ª14:37:18ª0ª 0 ih_officer NULL ih
(7 row(s) affected)
March 4, 2015 at 9:18 am
Being that you only want to give us 7 sample rows, maybe just copy and paste the insert statement for us together?
We can keep playing null detective instead, but if we're going to do that you should give us the exact text of each query you ran.
March 5, 2015 at 1:14 am
There are a couple of possible issues, have you tried just putting one of those fields into a variable and passing it through the Splitter
I did a simple test using my version of Jeff's splitter (without the ItemNumber) on one of the strings and it worked.
DECLARE @test-2 VARCHAR(8000)='01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª'
SELECT * FROM Reporting.SplitParam_test(@Test,'ª') WHERE ItemNumber=17
Returns the result Ryan
Also wouldn't it be better to do something like this rather than have all the cross applys
DECLARE @test-2 VARCHAR(8000)='01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª'
SELECT PvtData.*
FROM
(
SELECT
CASE ItemNumber
WHEN 5 THEN 'Surname'
WHEN 17 THEN 'ForeName'
WHEN 15 THEN 'JobTitle'
END ItemDesc
,Item
FROM Reporting.SplitParam_test(@Test,'ª')
WHERE ItemNumber in (5,15,17)
) rawdata
PIVOT
(
MAX(Item) FOR ItemDesc IN ([Surname],[ForeName],[JobTitle])
) PvtData
I also cant see the point of the Cross apply on the bi/ai columns unless you want one row PER element in each, again there are simpler ways to do that.
If you wanted to be really clever, you simply create a mapping table that has the element Id and a description, that way a simple join from ItemNumber to the table and you can do away with the case statement. eg
DECLARE @test-2 VARCHAR(8000)='01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª'
DECLARE @LkpTable TABLE
(
ElementNumber smallint
,ElementDesc varchar(100)
)
INSERT INTO @LkpTable
VALUES
(5,'Surname')
,(17,'ForeName')
,(15,'JobTitle')
SELECT PvtData.*
FROM
(
SELECT
ElementDesc
,Item
FROM Reporting.SplitParam_test(@Test,'ª')
JOIN @LkpTable ON ElementNumber=ItemNumber
) rawdata
PIVOT
(
MAX(Item) FOR ElementDesc IN ([Surname],[ForeName],[JobTitle])
) PvtData
This way you can control things a lot smoother.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 5, 2015 at 2:10 am
Sorry this is proving a nightmare.
I now get this when I try and insert using this code -
INSERT INTO [dbo].[co_audit_trail2]
(user_name, date_of_change, time_of_change, bi, ai, modified_table)
SELECT 'ryank'*'Feb 25 2015 9:08AM'*'09:07:58:913'*01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@mhs.org.ukª0ª0ª0ª ª0ª ª ª ª ªCustomer Service Advisorª0ª0ª0ª0.00ª ª ªª444ª444ª0ªª ªª ª0ª*01ªRC1ªMrªRªCottissªª4231ª0ª0ª0ª0ª1ª0ª0ªAsset Managementª0ªRickyªªricky.cottiss@mhs.org.ukª0ª0ª0ªª0ªª ª ª ªPolicy & Data Co-Ordinatorª0ª0ª0ª0.00ª ª ªª444ª444ª0ª25/02/2015ª ªª ª0ª*'ih_officer' UNION ALL
SELECT 'adamb'*'Feb 25 2015 1:24PM'*'13:24:16:993'*01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª*01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªª744ª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª*'ih_officer' UNION ALL
SELECT 'adamb'*'Feb 25 2015 1:24PM'*'13:24:17:853'*''*01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mhs.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª*'ih_officer' UNION ALL
SELECT 'ryank'*'Feb 27 2015 4:00PM'*'16:00:43:407'*
01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª19/08/2014ªJB7ª12/08/2014ª14:37:18ª0ª*01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª*'ih_officer' UNION ALL
SELECT 'ryank'*'Feb 27 2015 4:02PM'*'16:02:15:227'*01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª*01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª*'ih_officer' UNION ALL
SELECT 'joannab'*'Feb 27 2015 4:07PM'*'16:07:12:003'*01ªJD7ªMrsªJªDobinsonª01634 83194ªª0ª0ª1ª0ª1ª0ª0ªFoyersª0ªJulieªªjulie.dobinson@mhs.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ªªAB6ª23/12/2014ª11:28:25ª0ª*01ªJD7ªMrsªJªDobinsonª01634 83194ªª1ª0ª1ª0ª1ª1ª0ªFoyersª0ªJulieªªjulie.dobinson@mhs.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ª27/02/2015ªAB6ª23/12/2014ª11:28:25ª0ª*'ih_officer' UNION ALL
SELECT 'ryank'*'Mar 2 2015 9:35AM'*'09:35:57:170'*01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª*01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª02/03/2015ªJB7ª12/08/2014ª14:37:18ª0ª*'ih_officer' UNION ALL
SELECT 'jennyr'*'Mar 4 2015 3:23PM'*'15:23:13:777'*01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mhs.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª*01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mhs.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªª745ª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª*'ih_officer' UNION ALL
SELECT 'jennyr'*'Mar 4 2015 3:23PM'*'15:23:14:600'*''*01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mhs.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª*'ih_officer'
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Repairsª0ªAlainªªAlain'.
Msg 132, Level 15, State 1, Line 4
The label 'ª13' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'Repairsª0ªAlainªªAlain'.
Msg 132, Level 15, State 1, Line 5
The label 'ª13' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '-'.
Msg 132, Level 15, State 1, Line 7
The label 'ª14' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '-'.
Msg 132, Level 15, State 1, Line 8
The label 'ª14' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 8
The label 'ª14' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '83194'.
Msg 132, Level 15, State 1, Line 9
The label 'ª11' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '-'.
Msg 132, Level 15, State 1, Line 10
The label 'ª14' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 10
The label 'ª14' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '35'.
Msg 132, Level 15, State 1, Line 11
The label 'ª15' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '35'.
Msg 132, Level 15, State 1, Line 12
The label 'ª15' has already been declared. Label names must be unique within a query batch or stored procedure.
March 5, 2015 at 2:30 am
For a start you insert is completely malformed, replace the * with a comma, and you are missing single quotes around the bi/ai columns in the selects.
Just taking the first row it should look like this
SELECT
'ryank'
,'Feb 25 2015 9:08AM'
,'09:07:58:913'
,'01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@mhs.org.ukª0ª0ª0ª ª0ª ª ª ª ªCustomer Service Advisorª0ª0ª0ª0.00ª ª ªª444ª444ª0ªª ªª ª0ª'
,'01ªRC1ªMrªRªCottissªª4231ª0ª0ª0ª0ª1ª0ª0ªAsset Managementª0ªRickyªªricky.cottiss@mhs.org.ukª0ª0ª0ªª0ªª ª ª ªPolicy & Data Co-Ordinatorª0ª0ª0ª0.00ª ª ªª444ª444ª0ª25/02/2015ª ªª ª0ª'
,'ih_officer'
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 5, 2015 at 2:46 am
Thank you Jason.
INSERT INTO [dbo].[co_audit_trail2]
(user_name, date_of_change, time_of_change, bi, ai, modified_table)
SELECT 'ryank','Feb 25 2015 9:08AM','09:07:58:913','01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@mh.org.ukª0ª0ª0ª ª0ª ª ª ª ªCustomer Service Advisorª0ª0ª0ª0.00ª ª ªª444ª444ª0ªª ªª ª0ª,01ªRC1ªMrªRªCottissªª4231ª0ª0ª0ª0ª1ª0ª0ªAsset Managementª0ªRickyªªricky.cottiss@mh.org.ukª0ª0ª0ªª0ªª ª ª ªPolicy & Data Co-Ordinatorª0ª0ª0ª0.00ª ª ªª444ª444ª0ª25/02/2015ª ªª ª0ª','ih_officer' UNION ALL
SELECT 'adamb','Feb 25 2015 1:24PM','13:24:16:993','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mh.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mh.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªª744ª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','ih_officer' UNION ALL
SELECT 'adamb','Feb 25 2015 1:24PM','13:24:17:853',' ','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@mh.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Feb 27 2015 4:00PM','16:00:43:407','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª19/08/2014ªJB7ª12/08/2014ª14:37:18ª0ª,01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Feb 27 2015 4:02PM','16:02:15:227','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª,01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'joannab','Feb 27 2015 4:07PM','16:07:12:003','01ªJD7ªMrsªJªDobinsonª01634 83194ªª0ª0ª1ª0ª1ª0ª0ªFoyersª0ªJulieªªjulie.dobinson@mh.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ªªAB6ª23/12/2014ª11:28:25ª0ª,01ªJD7ªMrsªJªDobinsonª01634 83194ªª1ª0ª1ª0ª1ª1ª0ªFoyersª0ªJulieªªjulie.dobinson@mh.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ª27/02/2015ªAB6ª23/12/2014ª11:28:25ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Mar 2 2015 9:35AM','09:35:57:170','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª02/03/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'jennyr','Mar 4 2015 3:23PM','15:23:13:777','01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mh.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª,01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mh.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªª745ª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª','ih_officer' UNION ALL
SELECT 'jennyr','Mar 4 2015 3:23PM','15:23:14:600',' ','01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@mh.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª','ih_officer'
Now I get Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
I've imported into csv and all the columns are the same - so again do not understand the error
March 5, 2015 at 2:54 am
ignore last post - think I've worked it out - will post all code again shortly.
Sorry
March 5, 2015 at 3:13 am
TSQL Tryer (3/5/2015)
ignore last post - think I've worked it out - will post all code again shortly.Sorry
I was just about to send a response, its the ai/bi columns that haven't separated on a couple of the selects. (eg Selects 1, 3,4,5,6)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply