April 14, 2016 at 9:47 am
I have read, and read about the output clause prior to posting here.
Clearly I am at a bit of a loss because I have yet to get this working; that is why I came here asking for help in the first place.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 9:56 am
Reading your code, it is not clear what you want. As others already said, the error comes from a structure that looks like this:
SELECT ... FROM (INSERT ... SELECT ...)
That structure is not valid. The part between parentheses is a subquery and it has to return data. An INSERT statement does not return data.
Technically, the ways to fix this are either:
Rewrite to do stuff in sequence:
INSERT ... SELECT ...;
SELECT ... FROM (SELECT ...);
Use the OUTPUT clause (as suggested in the error message) to ensure that the INSERT does return data:
SELECT ... FROM (INSERT ... OUTPUT ... SELECT ...)
That last form is relatively unknown and for good reasons because it is dangerous when used inappropriately. What this does is execute the INSERT statement to store data in a table, and then also present some or all of the inserted data to the subquery. Now you can have a query that looks like a normal SELECT that actually inserts data (hidden in a subquery). Or you can have a single statement insert data into two tables at once. Can be very handy, but as I said, also very dangerous!
I don't know which solution is correct for you. You only post the query that is giving you problems (different versions, even - I now no longer know the correct one), without giving us more information. Can you post a full repro script: CREATE TABLE statements, INSERT statements to set up sample data, the code you are currently using, and the end result you need? Than we can actually help you get to a correct solution instead of just pointing at the part of the code where the error occurs and making blind stabs at what you are trying to achieve.
April 14, 2016 at 10:01 am
Here is code that works like intended. (EXCEPT it does not insert the results into a table)
When you run this you get a table of results back in ssms. I want these results in a table.
DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
--INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
INSERT @t VALUES (1,'GeoCode,ConfigSalesTaxRateStateGUID,StartDate,EndDate,CountyName')
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 10:13 am
Jeffery Williams (4/14/2016)
Here is code that works like intended. (EXCEPT it does not insert the results into a table)When you run this you get a table of results back in ssms. I want these results in a table.
DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
--INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
INSERT @t VALUES (1,'GeoCode,ConfigSalesTaxRateStateGUID,StartDate,EndDate,CountyName')
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
The following modification (should make it insert the data in a table:
DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
--INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
INSERT @t VALUES (1,'GeoCode,ConfigSalesTaxRateStateGUID,StartDate,EndDate,CountyName')
INSERT INTO Schema.TableName (EmployeeID, Certs)
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
April 14, 2016 at 10:31 am
Good grief! Thank you!!!
I actually just wrote this differently and have it working and deployed but I will keep this handy as well; thank you so much.
I found something I had written long ago in my Google Drive that I somehow missed my fist few searches? Anyway adapted it to this project.
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = LTRIM(RTRIM(@String)) --'Status,AccountingPeriodGUID,AccountingPeriodStaffingSupplierGUID,StaffingSupplierGUID'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = ','
DECLARE @SplitStartPos int = 1
PRINT cast(@StringToSplit as varchar(4000))
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
BEGIN
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
SELECT @DBIndexID, Value
FROM @SplitStringTable
END
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 11:32 am
Jeffery Williams (4/14/2016)
Good grief! Thank you!!!I actually just wrote this differently and have it working and deployed but I will keep this handy as well; thank you so much.
I found something I had written long ago in my Google Drive that I somehow missed my fist few searches? Anyway adapted it to this project.
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = LTRIM(RTRIM(@String)) --'Status,AccountingPeriodGUID,AccountingPeriodStaffingSupplierGUID,StaffingSupplierGUID'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = ','
DECLARE @SplitStartPos int = 1
PRINT cast(@StringToSplit as varchar(4000))
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
BEGIN
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
SELECT @DBIndexID, Value
FROM @SplitStringTable
END
Okay, now that you're starting to get into string splitting, you should take the time to read the article at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It's a much more efficient string splitter than the loop you have.
April 14, 2016 at 12:31 pm
Great, thank you. I will check it out!
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply