March 25, 2008 at 4:01 pm
Hello,
I have data in table A. I need to split up this data and put half in table B and half in table C. Table C will contain the primary key id from table B to maintain the relationship between the data.
Any idea how to do this?
March 25, 2008 at 4:17 pm
This sounds pretty simple, but you'll need to provide more information to get good help on this one. How about some table DDL and sample data. Better yet, how about posting what you have already tried as well.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2008 at 10:56 pm
[font="Verdana"]try this ...
Insert Into B Select * From A Where (the part you wants to insert into table B from table A)
Go
Insert Into C Select * From A Where (the part you wants to insert into table C from table A)
If you want to maintain relationship between table B and table C, assuming you have such data in the table A, first try to select those records from table A which you wants to insert into table B(means treat table B as Parent table) and then insert the remaining records into table C as it will be child table.
Go
Let us know,
Thanks,
Mahesh[/font]
MH-09-AM-8694
March 26, 2008 at 7:37 am
This sounds close but I don't see the part where table c has a reference to table b...
Normally what I would do (Which I don't think is the best way) Is:
Insert into B (values, values...) Select values, values... From A
set @MaxID = Select MAX(ID) From B
Insert into C (TableB_Id, values, values...) Select @MaxID, values, values... From A
Hope this makes sense.
March 26, 2008 at 8:37 am
This sounds pretty simple, but you'll need to provide more information to get good help on this one. How about some table DDL and sample data. Better yet, how about posting what you have already tried as well.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 26, 2008 at 8:40 am
I don't understand what your looking for. The example above is pretty much it. Do you want sample data or something? I'm just looking for a sample query and I can tailor it to my needs.
March 26, 2008 at 8:55 am
Did you read the link? What I want is in my post. I want you to mock up an example that we can work with. Help us help you. If all you do is give a very vague description of what you want, we are left to guess what you are looking for. Just as the previous poster gave you a solution and you said 'close'. The solution posted was close because the poster was basing it off of a guess on what you need instead of off of your example.
You're asking for help right? Why don't you do the work in providing a workable example including sample tables a,b,c and an example of how you want the data to look before and after? There are many of us out here that will attempt to create an example for you, but why not save the people helping you time by doing that up-front. Sample tables + sample data + sample results = quality help. It is also good to post what you have already tried so we can help you think through it.
Something like this is what we are looking for:
DECLARE @TableA TABLE (RowID int, RowValue varchar(10))
DECLARE @TableB TABLE (RowID int, RowValue varchar(10))
DECLARE @TableC TABLE (RowID int, BRowID int, RowValue varchar(10))
INSERT INTO @TableA
SELECT 1, 'one' UNION ALL
SELECT 2, 'two' UNION ALL
SELECT 3, 'three' UNION ALL
SELECT 4, 'four' UNION ALL
SELECT 5, 'five' UNION ALL
SELECT 6, 'six' UNION ALL
???????????????????
March 26, 2008 at 8:57 am
What you are asking for does not make sense. If you are going to put half of TableA's rows into TableB and half into TableC, what is with TableB's key being in TableC? How are the rows related?
March 26, 2008 at 9:12 am
Here goes:
DECLARE @TableA TABLE (RowID int, Name varchar(20), Address varchar(50))
DECLARE @TableB TABLE (RowID int, Name varchar(20))
DECLARE @TableC TABLE (RowID int, BRowID int, Address varchar(50))
INSERT INTO @TableA
SELECT 1, 'david', '4405 W South' UNION ALL
SELECT 2, 'mike', 'test address' UNION ALL
SELECT 3, 'john', 'address testing again' UNION ALL
SELECT 4, 'joe', '1 more time' UNION ALL
SELECT 5, 'david', 'finally a different address' UNION ALL
Table A is derived from a flat file with name, address..etc all in one row. Since 1 person could have multiple addresses (this is just an example) I would like to store their name once and be able to store/reference all of their addresses. I would do this by selecting all addresses that match BRowID x in tableC.
March 26, 2008 at 9:24 am
OK, we're getting there. Now, what would you like TableB and TableC to look like based off of the sample data you've provided for tableA?
TableB
????
TableC
????
March 26, 2008 at 9:30 am
Table B
1, 'david'
2, 'mike'
3, 'john'
4, 'joe'
Table C
1, '4405 W South'
2, 'test address'
3, 'address testing again'
4, '1 more time'
1, 'finally a different address'
March 26, 2008 at 9:53 am
Thanks. Your example is different that what I thought you were after. I was thinking that you wanted to split the data differently as in 1/2 of the rows going into each table (rows 1,2,3 into B and 4,5,6 into C). That is why it is important to use examples :). Thanks for being a good sport.
DECLARE @TableA TABLE (RowID int, Name varchar(20), Address varchar(50))
DECLARE @TableB TABLE (RowID int, Name varchar(20))
DECLARE @TableC TABLE (RowID int, Address varchar(50))
INSERT INTO @TableA
SELECT 1, 'david', '4405 W South' UNION ALL
SELECT 2, 'mike', 'test address' UNION ALL
SELECT 3, 'john', 'address testing again' UNION ALL
SELECT 4, 'joe', '1 more time' UNION ALL
SELECT 5, 'david', 'finally a different address'
INSERT INTO @TableB
SELECT MIN(RowID), Name
FROM @TableA
GROUP BY Name
INSERT INTO @TableC
SELECT b.RowID, a.Address
FROM @TableA a
INNER JOIN @TableB b
ON a.Name = b.Name
SELECT * FROM @TableB ORDER BY RowID
SELECT * FROM @TableC ORDER BY RowID
March 26, 2008 at 9:57 am
Sweet! Thanks for your help.
March 26, 2008 at 10:09 am
if you want to splil data from one table into two different tables and want to have a relationship among those two tables, u can not do that you need not do that as well, because you can obtain the information by doing an union on those two tables.
There wont be any relationship on those two tables at all.
March 26, 2008 at 10:17 am
What?? In his example, the relationship persists because the RowID is maintained between the two. With the example shown, you can re-link the Names and Addresses by running a simple JOIN, no UNION is needed.
SELECT Name, Address
FROM @TableB b
INNER JOIN @TableC c
ON b.RowID = c.RowID
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply