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.
| Author |
Topic |
|
someoneyeah
Starting Member
3 Posts |
 Posted - 2010-11-22 : 07:34:28
|
| Hi!I have a problem when I try to insert data into two tables. Basically, I want to insert a row into one of the tables (table1), get the scope identity and insert it together with an integer from a third table (table3) into the second table (table2). The problem is that I want this done multiple times. So, for example, if there are 10 values in table3, I want 10 insertions into table1 and then using those ten unique scope identities and the values from table3, 10 insertions into table2.I hope I have explained well enough for you to understant. If not, I will try and clarify it more. 10x |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-22 : 07:41:13
|
| There is an output clause on the insert statement. You can use that to get the identities allocated into a table variable and use it for the join.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
someoneyeah
Starting Member
3 Posts |
Posted - 2010-11-22 : 07:43:59
|
| Is there any chance you can give me a relevant link or example code? Thank you |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-22 : 07:50:27
|
| declare @t table (id int)insert tbl (col,col,col)output inserted.id into @t (id)select 1,2,3select * from @t==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
someoneyeah
Starting Member
3 Posts |
Posted - 2010-11-22 : 09:36:47
|
| Thank you, but, unfortunately, I still cannot figure out how to use it in my scenario. I am quite new to SQL Server |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-22 : 09:51:23
|
| That will give you a resultset of the IDs that have been allocated by the insert in @t. You can add any other column from the table that you need (like the primary key).You can then use the table variable @t to join to t3 and insert into t2.insert t2select t.id, t3.xxxfrom @t tjoin t3on t.id = t3.id==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-11-22 : 12:01:24
|
quote:
Originally posted by someoneyeah
Thank you, but, unfortunately, I still cannot figure out how to use it in my scenario. I am quite new to SQL Server
What you are doing is mimicking 1950's file system pointer chains in SQL with proprietary features. IDENTITY is a table property and not at all relational. Do you understand the concept of a key as a subset of attributes of an entity? Counting physical insertion attempts is not a key. Why don't you post what you are doing and let's try to design a schema instead of a file system based on physical storage? --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Problem with a multiple insert using scope identit