mike11d11


I'm updating a table on my SQL server with fields from tables in a linked server. This linked server has about 100-150 table names that all start with public.CF and was hoping I could just create some sort of loop to update my table with all the tables that start with public.CF* name. All the tables have the same fields names. Below is my statement I use to update my table, I would just want to run a loop and change out public.CFATY with the next table name like public.CFATZ. Any help would be greatly appreciated and would save me hours of work. Thanks

SELECT * FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM [public.CFATY]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Update WorkList
SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM [public.CFATY]') as Data
Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'





Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

K H Tan


you can find out the table name using sysobjects or INFORMATION_SCHEMA.TABLES
and use cursor or while loop to perform what you want.






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

could you show me an example of how this would be done, or point me in the direction of where I can get more details of how to use this.





Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

DannoCoy

Mike, not gonna guarantee that this code works perfectly, but should get you most of the way there...

---------------------------------------------------------------------------
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked server.
--
---------------------------------------------------------------------------

Declare
@Table_Name varchar(255),
@SQL varchar(max)

---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)

---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'SCH'

---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'public.CF%'

-- Open the cursor defined above
Open crsLinkedServerTables

Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process

-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.

Set @Sql = '
SELECT * FROM OPENQUERY(SCH,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM '
+
@table_name
+
''') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#] '

print @SQL
exec (@SQL) /* Better to use sp_executesql, but this is here as an example and should work */

set @SQL = '
Update WorkList
SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM ' + @table_name
''') as Data
Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''SCH'''

print @SQL
exec (@SQL) /* Better to use sp_executesql, but this is here as an example and should work */

-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name

End

---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------

drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

Thanks this is great. I will give it a shot.




Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

I tried your code above but it doesnt seem to be doing the updates to my accounts on the server. When it gets done it says 205 rows affected but the two columns that I'm trying to update aren't getting updated. I tried changing around the code to see any difference but it does the same. One thing I did notice is that you must be referencing some sort of stored procedure when it shows "exec sp_tables_ex" I'm running this from the Query analyzer and am choosing my database where my WorkList table is, but in the enterprise manager it doesnt show this stored procedure listed in the stored procedures section. I noticed it under the Master, can this be copied over to my database if this might be the problem

Declare
@Table_Name varchar(255),
@Sql Nvarchar(500)

---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)

---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'SCH'

---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'public.CFATY' --test 'public.CF%'

-- Open the cursor defined above
Open crsLinkedServerTables

Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process

-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.

Set @Sql = 'SELECT * FROM OPENQUERY(SCH,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM ' + @table_name + ''') AS A INNER JOIN WorkList AS B ON A.Account_ID = B.[ACCOUNT#] Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM ' + @table_name + ''') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''SCH'''

print @Sql
exec (@Sql) /* Better to use sp_executesql, but this is here as an example and should work */

-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name

End

---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------

drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

DannoCoy

What version of SQL are u running   I created this in SQL 2005.  If the script didnt' find the stored procedure, it would be getting and error (you don't need to copy it to your database because it is executing it from the master db). 

PS.  The code you posted has the update included with the select statement.  You must split these apart as I did in my example.  The code you posted will NOT work...

Set @Sql = "Select...."

Exec (@Sql)

Set @Sql = "Update..."

Exec (@Sql)

PS.  You are not storing the result of your select query anywhere, so why are you using it   If you don't need it, get rid of the "Select..." on only do the "Update.." portion. 

You might try putting a "select * from #tLinkedServerTables" before the "drop table #tLinkedServerTables" at the end to see if this is populating with your table names (this is probably where you are getting the 205 rows affected statement from).

 

 






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

I'm running this on SQL 2000. Yeh I'm not getting any errors, it just seems like its creating the temp tables and that's it, and not running the SQL statement. I even took out the first part and ran just the update portion and got the same results. Your right the 205 is exactly how many tables are on the linked server and about %90 percent of those are the ones that start with public.CF. You can see that I changed it to look for just one public.CFATY and it still creates the 205 tables this is what I changed below...

---------------------------------------------------------------------------
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked server.
--
---------------------------------------------------------------------------

Declare
@Table_Name varchar(255),
@Sql Nvarchar(500)

---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)

---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'SCH'

---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'public.CFATY' --test 'public.CF%'

-- Open the cursor defined above
Open crsLinkedServerTables

Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process

-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.


Set @Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM ' + @table_name + ''') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''SCH'''

print @Sql
exec (@Sql)

-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name

End

---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------
select * from #tLinkedServerTables

drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

Man thanks so much for all your help. I think I got it. I changed around a couple things but it works now.

drop table #tLinkedServerTables
---------------------------------------------------------------------------
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked server.
--
---------------------------------------------------------------------------

Declare
@Table_Name varchar(255),
@Sql Nvarchar(500)

---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)

---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'

---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'Table'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%' --test 'public.CF%'

-- Open the cursor defined above
Open crsLinkedServerTables

Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process

-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.

Set @Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.' + @table_name + ''') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''

print @Sql
exec (@Sql) /* Better to use sp_executesql, but this is here as an example and should work */

-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name

End

---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------
select * from #tLinkedServerTables

drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

Do you know why this seems to work only using the Query Analyzer I was wanting to put this into the SQL server agent Jobs and have it runs in the evenings on a schedule but it errors out.




Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

DannoCoy

Mike, best way to to do this is to create a stored procedure (from the script) and then run the stored procedure from your job.  This way if you need to modify it, you don't have to go into the job, just edit the stored procedure...Need to tell us what the error is for us to be able to help... 

Looks like the problem (based on the code example you said works) is that the original linked server was not the correct one.  Glad you got it going.

See http://msdn2.microsoft.com/en-us/library/ms345415.aspx  (describes how to create a stored procedure).  If you search through the SQL Server On-line books there is probably an example of how to set up the job you want to run in Agent.

 






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

I actually have two linked Servers, one is named SCH and the other is TLRA. It doesn't give me a specific error, it just says step failed. This is what it shows in the history for this job...

... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_... The step failed.

I also tried to make a stored procedure and I get an error when I execute it in a job as well. My job run statement exec [SCH CF UPDATE]. This is the error I get below.

Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.

I'll see what I can find out about the ANSI_NULLS and ANSI_WARNINGS.






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

I tried recreating my stored procedure from the query analyzer with this below but still same results when I exec the stored procedure from the jobs.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE [DBO].[TLRA CF UPDATE] AS

---------------------------------------------------------------------------
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked server.
--
---------------------------------------------------------------------------

Declare
@Table_Name varchar(255),
@Sql Nvarchar(500)

---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)

---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'

---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'TABLE'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%'
and table_name not like 'CF1' --test 'public.CF%'

-- Open the cursor defined above
Open crsLinkedServerTables

Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process

-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.

Set @Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.' + @table_name + ''') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''

print @Sql

exec (@Sql) /* Better to use sp_executesql, but this is here as an example and should work */

-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name

End

---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------

drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO






Re: Is there a way to loop through tables in update TSQL statements, based on name criteria?

mike11d11

Never mind, I figured it out. There where tables still showing on system that are not on the linked server. my system wasn't getting updated and it was failed when it looped to a table that was no longer there. Thanks for all your help anyways!