April 14, 2016 at 8:34 am
If someone could help that would be great. I have this script that thrown the results to the screen but I need to insert the results into 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 8:38 am
Jeffery Williams (4/14/2016)
If someone could help that would be great. I have this script that thrown the results to the screen but I need to insert the results into 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)
Sounds straightforward. What problem are you having?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 8:38 am
Do you mean like using INSERT INTO YourTable SELECT ....?
April 14, 2016 at 8:39 am
P.S. I tried an insert in the sample below but is says I need a FOR OUTPUT and this is where I am stuck, don't know where to put that or what the syntax would be.
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
(
INEERT INTO TableName (field, field)
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 8:41 am
Yes INSERT INTO.
I am getting an error that I need an output clause. Sorry, not sure where to put that or what syntax.
Exact Error:
Msg 10716, Level 15, State 1, Line 58
A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 8:45 am
Jeffery Williams (4/14/2016)
P.S. I tried an insert in the sample below but is says I need a FOR OUTPUT and this is where I am stuck, don't know where to put that or what the syntax would be.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
(
INEERT INTO TableName (field, field)
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)
If you tried this exact sample, you should have received an error, because INSERT is misspelled.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 8:48 am
No I put this together for purposes of the post; my bad.
My actual code is the following:
(I have a string with a , as a deliminator. I need to parse this out. It is a list of columns that are included in an index.)
ALTER PROCEDUREETLParseIndexFieldList
as
DECLARE @DBIndexIDas int
,@Stringas varchar(4000)
,@ToProcessCountas int
DECLARE@ColumnStringTABLE
(DBIndexID int, String varchar(4000))
DECLARE @IndexColumnsTABLE
(DBIndexID int, ColumnName varchar(200))
DECLARE@IndexesToProcessTABLE
(DBIndexID int, Processed bit)
BEGIN
INSERT INTO @IndexesToProcess
(DBIndexID, Processed)
SELECT DBIndexID, 0
FROM [etl].[dbo].[ETLDBIndexes]
WHERE DBIndexID NOT IN (SELECT DBIndexID FROM [etl].[dbo].[ETLDBIndexColumn])
AND isPrimaryKey = 0
AND isUnique = 1
END
BEGIN
SELECT TOP 1 @DBIndexID = i.DBIndexID, @String = i.IndexColumns
FROM [etl].[dbo].[ETLDBIndexes] i
JOIN @IndexesToProcess p on p.DBIndexID = i.DBIndexID
WHERE p.Processed = 0
END
INSERT @ColumnString VALUES (@DBIndexID,@String)
SELECT DBIndexID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS String
FROM
(
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
SELECT DBIndexID,CAST('<XMLRoot><RowData>' + REPLACE(String,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @ColumnString
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
SELECT * FROM ETLDBIndexColumn
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 8:49 am
No I put this together for purposes of the post; my bad.
My actual code is the following:
(I have a string with a , as a deliminator. I need to parse this out. It is a list of columns that are included in an index.)
ALTER PROCEDUREETLParseIndexFieldList
as
DECLARE @DBIndexIDas int
,@Stringas varchar(4000)
,@ToProcessCountas int
DECLARE@ColumnStringTABLE
(DBIndexID int, String varchar(4000))
DECLARE @IndexColumnsTABLE
(DBIndexID int, ColumnName varchar(200))
DECLARE@IndexesToProcessTABLE
(DBIndexID int, Processed bit)
BEGIN
INSERT INTO @IndexesToProcess
(DBIndexID, Processed)
SELECT DBIndexID, 0
FROM [etl].[dbo].[ETLDBIndexes]
WHERE DBIndexID NOT IN (SELECT DBIndexID FROM [etl].[dbo].[ETLDBIndexColumn])
AND isPrimaryKey = 0
AND isUnique = 1
END
BEGIN
SELECT TOP 1 @DBIndexID = i.DBIndexID, @String = i.IndexColumns
FROM [etl].[dbo].[ETLDBIndexes] i
JOIN @IndexesToProcess p on p.DBIndexID = i.DBIndexID
WHERE p.Processed = 0
END
INSERT @ColumnString VALUES (@DBIndexID,@String)
SELECT DBIndexID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS String
FROM
(
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
SELECT DBIndexID,CAST('<XMLRoot><RowData>' + REPLACE(String,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @ColumnString
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
SELECT * FROM ETLDBIndexColumn
INSERT INTO ETLDBIndexColumn
(DBIndexID, ColumnName)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 8:58 am
INSERT INTO goes before the whole statement, not before the SELECT from the derived table.
April 14, 2016 at 9:04 am
I have an insert into at the top and this is where I am grabbing the source data and placing into derived table. Then I need the results into a physical table.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 9:22 am
You have a construction like this
INSERT ...
SELECT ... FROM
(INSERT ...
SELECT ...
)
INSERT SELECT does not return a result set, so your outer INSERT fails.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2016 at 9:22 am
I THOUGHT I figured it out with another script.
Can someone please lend a hand on this one? Perhaps just throw the insert into my script so I can see a live example of this?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 9:24 am
Jeffery Williams (4/14/2016)
Yes INSERT INTO.I am getting an error that I need an output clause. Sorry, not sure where to put that or what syntax.
Exact Error:
Msg 10716, Level 15, State 1, Line 58
A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause.
Here is the info on the OUTPUT Clause. The reason for this is that a FROM clause requires a rowset and INSERT, UPDATE, DELETE, and MERGE do not return anything unless you specify an OUTPUT clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 14, 2016 at 9:37 am
The script is working except I can not insert into a table the results that are returned.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 14, 2016 at 9:40 am
Jeffery Williams (4/14/2016)
The script is working except I can not insert into a table the results that are returned.
Both Drew and I have explained the problem. Please take the time to reread what we have written.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply