Combining tables [modified]
-
I am working on combining two tables(tbl1,tbl2) from one database into one table(tbl3) in a seperate database. tbl1 and tbl2 have the same column names as tbl3 but they lack a location ID column which will be used in tbl3. Currently I can get the information transferred over to tbl3 via SELECT * INTO tbl3 FROM tbl1--tbl2, tbl1 and tbl2 both have the same primary key column but will have different locationID columns in the new table. I'm pretty sure I will have to use the SET IDENTITY_INSERT ON for tbl3 but how can I keep track of which table the data is coming from? Also how can I set the location ID, as this coincides with the primary field. I am trying to do all of this in a stored procedure. I was thinking of setting the tbl2 ID values to '100 + originalValue' as both tables currently have the same values in the ID column. Thanks in advance for any help. -- modified at 17:53 Thursday 5th October, 2006
A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder An American football fan - Go Seahawks! Lil Turtle
-
I am working on combining two tables(tbl1,tbl2) from one database into one table(tbl3) in a seperate database. tbl1 and tbl2 have the same column names as tbl3 but they lack a location ID column which will be used in tbl3. Currently I can get the information transferred over to tbl3 via SELECT * INTO tbl3 FROM tbl1--tbl2, tbl1 and tbl2 both have the same primary key column but will have different locationID columns in the new table. I'm pretty sure I will have to use the SET IDENTITY_INSERT ON for tbl3 but how can I keep track of which table the data is coming from? Also how can I set the location ID, as this coincides with the primary field. I am trying to do all of this in a stored procedure. I was thinking of setting the tbl2 ID values to '100 + originalValue' as both tables currently have the same values in the ID column. Thanks in advance for any help. -- modified at 17:53 Thursday 5th October, 2006
A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder An American football fan - Go Seahawks! Lil Turtle
Add a new field to tbl3, say, Source varchar(10) Insert Into tbl3 Select *, 'tbl1' /* Assuming Source field is the last field */ From tbl1 Insert Into tbl3 Select *, 'tbl2' /* Assuming Source field is the last field */ From tbl2
Farhan Noor Qureshi