Update different tables based on data in one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update different tables based on data in one table

Author

Topic

capella07
Starting Member

46 Posts

Posted - 2007-09-11 : 16:20:12

This is kind of a convoluted process I'm trying to set up here, so I'll do my best to be clear on what I'm trying to accomplish.

In a DTS (no, this is not a specific DTS question!) I am populating a temporary table with three columns from text files. The three columns in the temp table include the text from the file, and a column for a) which table the text goes to and b) the ID of which record in that table the text actually gets written to. So, it's something like:

Table name: tmpCommentsTable
Columns: comment tableID recordID
Row1: comment: Some text, tableID: 7, recordID: 2050
Row2: comment: Some text2, tableID: 7, recordID: 2051
Row3: comment: Some text3, tableID: 10, recordID: 945
Row4: comment: Some text4, tableID: 10, recordID: 947

Hope that makes sense - I can't figure out how to accurately represent a table in this forum post, so that's the best I can do.

So, with that information, I need to iterate through each row in the temp table and on each row, get the tableID and the recordID and insert the comment into that corresponding table/record combination.

I'd appreciate it if someone could help me hash this out.

Thanks!

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-11 : 16:44:43

fyi to represent tables just script out the definition of the table.

Assuming you have a table with tableID, tableName ex: TableXRef
Something like this. (warning syntax not checked)

Declare @minid int
Declare @tablename sysname
Declare @tableid int
Declare @dynsql as varchar(4000)

create table #InsertTables(RowID int identity(1,1), TableName sysname)

insert into #InsertTables(TableName, TableID)
select distinct B.TableName, B.TableID from
tmpCommentsTable A inner join TableXRef B on A.tableID = B.tableID
order by B.TableName Asc

set @minid = -1

select @minid = isnull(min(rowid),-1) from #InsertTables where rowid > @minid

while (@minid > 1)
begin

select @tablename = TableName, @tableID = TableID from #InsertTables where rowid = @minid

select @dynsql = 'insert into ' + @tablename + '(recordid,comment)
select recordid,comment from tmpCommentsTable where tableid = ' + cast(@tableid as varchar)

exec sp_executesql @dynsql

select @minid = isnull(min(rowid),-1) from #InsertTables where rowid > @minid

end

Ps: If you are interested in a high paying SQL job in Connecticut CT, please give a shout or send me your resume to ValterBorges@msn.com

capella07
Starting Member

46 Posts

Posted - 2007-09-11 : 17:19:52

Hey, ValterBorges. Thanks for replying.

I'm honestly not getting what you did in that example code. Unfortunately I'm not very experienced with SQL. The "Create Table" part, for instance, confused me. I'm inserting the comment column into existing tables defined by the tableID & recordID columns. Maybe that's what you're trying to do in your (pseudo)code, and I just don't understand the code (which is the likely scenario!).

Could you clarify what's going on in your example?

Thanks!

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-11 : 18:01:22

Sure

1. Create a temp table with a sequential id for each distinct table.
This is so we can iterate through each distinct table in order.

2. Loop thru each table and create sql statement that will get the records to insert into each table.

3. Execute that statement.

Think about it if you did this manually you might begin by choose tableid 1 (tableA) and doing an insert as such:

insert into tableA(comments,recordid)
select comments,recordid
from tmpCommentsTable
where tableid = 1

The code I provided creates all these insert statements for you and executes them.

capella07
Starting Member

46 Posts

Posted - 2007-09-12 : 09:14:54

Ah! Okay. I get the idea. I'll work with that and see what I can do (and get back here if I have any more questions).

Thanks a lot!

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources