dynamic insert of records - SQL Server Forums

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)

dynamic insert of records

Author

Topic

apociecha
Starting Member

17 Posts

Posted - 2003-02-20 : 17:55:02

I need to write a stored proc. which will INSERT records into one of 5 tables (a, b, c, d, e) from table X. Indicator field in table X stores values 1, 2, 3, 4, 5 which determines which one of the 5 tables the records should be added to.

I know that I could write 5 INSERT statements and filter the records in a WHERE clause, but is there a more dynamic way of doing this?

Thanks,
Agnes

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-20 : 18:11:55

No. You cannot INSERT into more than one table at a time.

OK, I have to ask: WHY are you inserting into 5 different tables like this? Why can't they stay in a single table like they are now?

apociecha
Starting Member

17 Posts

Posted - 2003-02-20 : 18:32:31

The decision for 5 tables was someone else's and I'm still in the process to convince 'em to combine it into 1. I thought I'd ask the question in case I'm successful.

I come from a procedural programming background and I'm trying to UNLEARN that method, so pardon the question, but, for future reference, could I build a dynamic sql statement like this in any of the DTS tasks?

Agnes

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-20 : 19:24:33

No, because dynamic SQL still won't change the fact that you have to do 5 separate INSERTs. But, you can just do this:

INSERT INTO a
SELECT * FROM X WHERE Indicator=1

INSERT INTO b
SELECT * FROM X WHERE Indicator=2

INSERT INTO c
SELECT * FROM X WHERE Indicator=3

INSERT INTO d
SELECT * FROM X WHERE Indicator=4

INSERT INTO e
SELECT * FROM X WHERE Indicator=5

The only reason you'd want to use dynamic SQL for this is if the indicators and/or the destination tables were highly variable, meaning they would change from day to day but always follow a pattern. If you're only gonna have 5 tables it's better to hard code them like shown here. If you're doing this from DTS just set up 5 separate transforms and use the SELECT statement as the source for each table (remove the INSERT clause from each)

apociecha
Starting Member

17 Posts

Posted - 2003-02-20 : 23:17:51

I thought at first that maybe I could use a parameter as my destination table, but realized that isn't a possibility.

Thanks very much for your help and insight...it's much appreciated.

Agnes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources