April 20, 2016 at 1:19 pm
Hello,
I have an event process routine that creates a work table with one record in it with multiple columns that are to be used to update a production table. How do I determine how many columns are in the current work table and then how do I pass the column name(s) to the insert stored procedure that will be used to update the production table?
Any ideas?
Thanks,
JP
April 20, 2016 at 1:52 pm
jpgillum (4/20/2016)
Hello,I have an event process routine that creates a work table with one record in it with multiple columns that are to be used to update a production table. How do I determine how many columns are in the current work table and then how do I pass the column name(s) to the insert stored procedure that will be used to update the production table?
Any ideas?
Thanks,
JP
Based on the very vague requirements, looks like a job for dynamic SQL. Crystal ball s in the shop so hard to give you much more of an answer since no one here can see what you see.
You really need to give us more information if you want a better answer. Remember that we are volunteers offer our assistance as we have time.
April 25, 2016 at 1:25 pm
I have the following SQL that creates the output I need to capture in a variable like @SQLstr so that I can execute the string:
exec(@SQLstr)
But I get the error message:
Msg 116, Level 16, State 1, Line 29
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
When I try to set @SQLstr = the select statement. See below.
The result set is a number of update statement.
Any ideas??
---- Declare Variables
declare @Utablename varchar(200) = 'JP_CDM.case_reopen_block_history'
declare @UtableKey varchar(200) = 'case_reopen_block_history_id'
declare @Wtablename varchar(200) = 'case_reopen_block_history_insert_hld'
declare @WtableKey varchar(200) = 'case_reopen_block_history_id'
declare @WtableKeyV int = 1
declare @SQLstr varchar (4000)
declare @get_case_key varchar(35) = 'case_id'
declare @case_id int = 1
declare @surrogate_Pkey_str varchar(100) = @Utablename + '.' + 'case_id'
Set @SQLstr =
(select ' UPDATE ' + @Utablename as ' ',
+ ' SET ' as ' ',
+ @Utablename + '.' + column_name as ' ',
+ ' = ' as ' ',
+ 'work' + '.' + @Wtablename + '.' + column_name as ' ',
+ ' from ' + 'work' + '.' + @Wtablename as ' ',
+ ' where ' + @surrogate_Pkey_str as ' ',
+ ' = ' + convert(varchar (100), @WtableKeyV) as ' '
--INTO #tempupdatestr
FROM information_schema.columns isc
where table_schema = 'work'
and column_name <> 'case_id'
and Table_name = @Wtablename)
April 25, 2016 at 1:28 pm
Here is a sample of the output I want to execute via @SQLstr
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.date_reopen_block_from_date = work.case_reopen_block_history_insert_hld.date_reopen_block_from_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.date_reopen_block_thru_date = work.case_reopen_block_history_insert_hld.date_reopen_block_thru_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.record_state = work.case_reopen_block_history_insert_hld.record_state from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.record_source = work.case_reopen_block_history_insert_hld.record_source from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.create_user = work.case_reopen_block_history_insert_hld.create_user from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.create_date = work.case_reopen_block_history_insert_hld.create_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.maint_user = work.case_reopen_block_history_insert_hld.maint_user from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
UPDATE JP_CDM.case_reopen_block_history SET JP_CDM.case_reopen_block_history.maint_date = work.case_reopen_block_history_insert_hld.maint_date from work.case_reopen_block_history_insert_hld where JP_CDM.case_reopen_block_history.case_id = 1
April 25, 2016 at 1:48 pm
i use something like this to generate models of both an insert and an update statement, assuming a staging table of the same name;
does this get you started?
you would run EXEC sp_colzu JP_CDM to get code to review
IF OBJECT_ID('[dbo].[sp_colzu]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_colzu]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Script Creates Update Table Template featuring QuoteNamed Column names for a given table
--#################################################################################################
CREATE PROCEDURE [dbo].[sp_colzu]
@Tablename SYSNAME
AS
BEGIN
IF LEFT(@Tablename, 1) = '['
AND LEFT(REVERSE(@Tablename), 1) = ']'
SET @Tablename = REPLACE(REPLACE(@Tablename, '[', ''), ']', '')
IF LEFT(@Tablename, 1) = '#'
BEGIN
SELECT DISTINCT
t.name,
InsertCommand =
'INSERT INTO ' + quotename(t.name) + '(' + sq.IColumns + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ' + REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+ ' FROM [Stage' + t.name + '] MySource' + CHAR(13) + CHAR(10)
+ ' LEFT OUTER JOIN ' + quotename( t.name) + 'MyTarget ' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ ' AND MySource.[Col2] = MyTarget.[Col2]' + CHAR(13) + CHAR(10)
+ ' WHERE MyTarget.[PK] IS NULL',
UpdateCommand =
'UPDATE MyTarget ' + CHAR(13) + CHAR(10)
+ 'SET ' + sq.Columns + ' ' + CHAR(13) + CHAR(10)
+ '--SELECT * ' + CHAR(13) + CHAR(10)
+ ' FROM [Staging' + t.name + '] MySource ' + CHAR(13) + CHAR(10)
+ ' INNER JOIN ' + quotename(t.name) + ' MyTarget ' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ ' AND MySource.[Col2] = MyTarget.[Col2]'
FROM tempdb.sys.tables t
JOIN (SELECT
OBJECT_ID,
Columns = STUFF((SELECT
',' + CHAR(13) + CHAR(10) + ' MyTarget.' + QUOTENAME(sc.name) + SPACE(30 - LEN(sc.name)) + ' = MySource.' + QUOTENAME(sc.name)
FROM tempdb.sys.columns sc
WHERE sc.object_id = s.object_id
ORDER BY sc.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
IColumns = STUFF((SELECT
',' + QUOTENAME(si.name)
FROM tempdb.sys.columns si
WHERE si.object_id = s.object_id
ORDER BY si.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM tempdb.sys.columns s) sq
ON t.object_id = sq.object_id
WHERE t.object_id = object_id('tempdb.dbo.' + @Tablename)
END
ELSE
BEGIN
SELECT DISTINCT
t.name,
InsertCommand =
'INSERT INTO ' + quotename( t.name) + '(' + sq.IColumns + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ' + REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+ ' FROM [Stage' +t.name + '] MySource' + CHAR(13) + CHAR(10)
+ ' LEFT OUTER JOIN ' + quotename( t.name) + 'MyTarget ' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ ' AND MySource.[Col2] = MyTarget.[Col2]' + CHAR(13) + CHAR(10)
+ ' WHERE MyTarget.[PK] IS NULL',
UpdateCommand =
'UPDATE MyTarget ' + CHAR(13) + CHAR(10)
+ 'SET ' + sq.Columns + ' ' + CHAR(13) + CHAR(10)
+ '--SELECT * ' + CHAR(13) + CHAR(10)
+ 'FROM [Staging' + t.name + '] MySource ' + CHAR(13) + CHAR(10)
+ 'INNER JOIN ' + quotename(t.name) + ' MyTarget ' + CHAR(13) + CHAR(10)
+ 'ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ 'AND MySource.[Col2] = MyTarget.[Col2]'
FROM sys.objects t
JOIN (SELECT
OBJECT_ID,
Columns = STUFF((SELECT
',' + CHAR(13) + CHAR(10) + ' MyTarget.' + QUOTENAME(sc.name) + SPACE(30 - LEN(sc.name)) + ' = MySource.' + QUOTENAME(sc.name)
FROM sys.columns sc
WHERE sc.object_id = s.object_id
ORDER BY sc.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
IColumns = STUFF((SELECT
',' + QUOTENAME(si.name)
FROM sys.columns si
WHERE si.object_id = s.object_id
ORDER BY si.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s) sq
ON t.object_id = sq.object_id
WHERE t.name = @Tablename
END --ELSE
END --PROC
GO
--#################################################################################################
--Mark as a system object
EXECUTE sp_ms_marksystemobject '[dbo].[sp_colzu]'
--#################################################################################################
Lowell
April 25, 2016 at 2:40 pm
Thanks,
Yes, this gets me started. I do have a question about updating multiple rows.
This example seems to only update one column in the target table.
Also the staging table will only have the columns needing updating and not all the columns in the table.
So I read information_schema.columns to get the staging table column names to update.
I like your strategy however, it changes the direction I was going. But I am looking for what works so I'll try to modify your strategy to see if I can make it meet my needs.
Any other ideas? Do you need more info from me?
Thanks
JP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply