January 19, 2012 at 12:14 pm
I have 2 tables
A and B
Table A columns:
Name -nvarchar
ID1 -nvarchar
ID2 -nvarchar
CatID -int
Table B(ID(primary key(Is identity)),Id_AB(FK),col6,col7,col8,col9,col10)
Id - int ,not null
id_ab - int ,null
col5 -nvarchar
col6 -nvarchar
col7 -bit,null
col8 -bit,null
col9 -bit,null
col10 -nvarchar
The table already has around 5000+ rows.
But i now I have a situation where for every new row in table A
with ID1 having
a new value say '12' and ID2 having a new value '14'
I need to insert 4 new rows
in Table B with 4 new rows as below
I need to insert 4 new rows in table B with values :
1st row----
(Id -incremental value
id_ab - 1
col5 - will have ID1 value
col6- will have Name value
col7 -will have True
col8 -will have True
col9 -will have True
col10 -will have ABC.mdb)
------
2nd row -
(Id -incremental value
id_ab - 2
col5 - will have ID1 value
col6- will have Name value
col7 -will have True
col8 -will have True
col9 -will have True
col10 -will have ABC.mdb)
----
3rd row-
(Id -incremental value
id_ab - 1
col5 - will have ID2 value
col6- will have Name value
col7 -will have True
col8 -will have True
col9 -will have True
col10 -will have ABC.mdb)
----
4th row
(Id -incremental value
id_ab - 2
col5 - will have ID2 value
col6- will have Name value
col7 -will have True
col8 -will have True
col9 -will have True
col10 -will have ABC.mdb)
Sample data:
Eg:
Table A already has 5000+ rows
Table A
Name ID1 ID2 CatID
ABC 101 102 1
DEF 111 222 15
Table B
Id id_ab col5 col6 col7 col8 col9 col10
10001 12 101 ABC True True True ABC.mdb
10002 14 101 ABC True True True ABC.mdb
10003 12 102 ABC True True True ABC.mdb
10004 14 102 ABC True True True ABC.mdb
-----------------------------------------------------------------------------
10001 12 111 DEF True True True ABC.mdb
10002 14 111 DEF True True True ABC.mdb
10003 12 222 DEF True True True ABC.mdb
10004 14 222 DEF True True True ABC.mdb
So for every new insert of row in Table A where CatID has values of 1,2,3,5,8,9,10,11,12,13,14,15 only --->Table B must have the above 4 inserts.
Col (Id) in table B is an identity column so it auto increments by 1 every time a new row is inserted.
January 19, 2012 at 1:38 pm
It is very unclear what you are trying to do here. Perhaps if you posted some ddl (create table statements), sample data (insert statements) and a clear explanation of what you are trying to do we can help. Take a look at the first link in my signature for best practices on posting data to support your question. Yes it takes some work on your end, but the help you receive is totally free and without that kind of detail the best you will get is a best guess as to what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 11:59 am
The easiest way to compound rows is to CROSS JOIN to another set. The query below returns one row for each record from the system table that stores object names:
SELECTo.id, o.name
FROMsys.sysobjects o
WHEREo.id BETWEEN 1 AND 10
ORDER BY o.id
In order to return 4 rows for each record from the same table, we CROSS JOIN to a table that we create on the fly that has four rows. Here is the query that creates for rows:
SELECT 1 AS RowID UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
Now we CROSS JOIN our original query with the four rows:
SELECTo.id, r.RowID, o.name
FROMsys.sysobjects o
CROSS JOIN (SELECT 1 AS RowID UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) r
WHEREo.id BETWEEN 1 AND 10
ORDER BY o.id,
r.RowID
If you want to modify the second ID, you can use the RowID from the second table to perform some calculations.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply