0

How do I use sp_MSforeachtable procedure to rewrite the script below? (or another way to loop through this instead of listing each command separately?)

insert into KSN001.WI_OCC_DATA select * from KSN000.WI_OCC_DATA where state_cd='01';

insert into KSN002.WI_OCC_DATA select * from KSN000.WI_OCC_DATA where state_cd='02';

insert into KSN003.WI_OCC_DATA select * from KSN000.WI_OCC_DATA where state_cd='03';

insert into KSN004.WI_OCC_DATA select * from KSN000.WI_OCC_DATA where state_cd='04';

insert into KSN005.WI_OCC_DATA select * from KSN000.WI_OCC_DATA where state_cd='05';

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Veronica Yost
  • 1
  • 1
  • You could use ```sp_MSforeachtable``` if you are doing this for all the tables in your database. If not, you could use a ```WHILE``` to do this for the tables you want. see if this helps: [link](https://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables) – Dark Eagle May 28 '21 at 15:15
  • How do I use sp_MSforeachtable in this case? – Veronica Yost May 28 '21 at 15:26
  • If KSNxxx is the database name and WI_OCC_DATA is the table name, you could first use sp_MSforeachDB, which iterates through all your databases so you can reach a certain table. It has a body with BEGIN and END, so you can have your own statements within. Inside the body, you can use sp_MSforeachtable if you want to execute the code on all tables. Otherwise, you can just declare a variable with the name "WI_OCC_DATA" and fetch the table with this name in the current database, then do your code. Since your statements involve different databases, you can use nesting. – Dark Eagle May 28 '21 at 15:36
  • Also, I don't know how you drop the table and then insert into it. Do you mean ```DELETE TABLE``` instead? – Dark Eagle May 28 '21 at 15:37

0 Answers0