SQL Server - Help with simple loop and inserting multiple records
Hi..
I have a table of Names. I need to insert 3 records into a new table for each name.
The NAMES table looks like this
NAME SSN
BROWN, JOHN 123-45-6789
GREEN, SUE 000-00-0000
I need to loop through the records and add 3 records for each
NAME SSN FEE
BROWN, JOHN 123-45-6789 25.00
BROWN, JOHN 123-45-6789 50.00
BROWN, JOHN 123-45-6789 75.00
GREEN, SUE 000-00-0000 25.00
GREEN, SUE 000-00-0000 50.00
GREEN, SUE 000-00-0000 75.00
I have a table of Names. I need to insert 3 records into a new table for each name.
The NAMES table looks like this
NAME SSN
BROWN, JOHN 123-45-6789
GREEN, SUE 000-00-0000
I need to loop through the records and add 3 records for each
NAME SSN FEE
BROWN, JOHN 123-45-6789 25.00
BROWN, JOHN 123-45-6789 50.00
BROWN, JOHN 123-45-6789 75.00
GREEN, SUE 000-00-0000 25.00
GREEN, SUE 000-00-0000 50.00
GREEN, SUE 000-00-0000 75.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
run this to see.
hth
Mike
select 'BROWN, JOHN' as 'Name', '123-45-6789' as 'SSN' into Names
insert into Names values ('GREEN, SUE', '000-00-0000')
create table NewTable (Name varchar(64), SSn varchar(11), Amount money)
insert into NewTable
select Name, SSN, 25 from Names
insert into NewTable
select Name, SSN, 50 from Names
insert into NewTable
select Name, SSN, 75 from Names
select * from NewTable order by Name, SSN, Amounthth
Mike
ASKER
I have hundreds of names... need to query the names into a temp table and for each records insert 3 records into a new table... by loop the temp table doing inserts
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How long is that statement going to be for hundreds of names?
"I have hundreds of names... need to query the names into a temp table and for each records insert 3 records into a new table... by loop the temp table doing inserts "
Don't use a loop: see my answer above.
Don't use a loop: see my answer above.
ASKER
How long is that statement going to be...? I have 987 names
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So I need 987 lines of
INSERT INTO @Names
SELECT 'BROWN, JOHN', '123-45-6789' UNION
SELECT 'GREEN, SUE', '000-00-0000'
SELECT 'BLUE, BOB', '123-45-6789' UNION
SELECT 'WHITE, MARY', '000-00-0000'.............
INSERT INTO @Names
SELECT 'BROWN, JOHN', '123-45-6789' UNION
SELECT 'GREEN, SUE', '000-00-0000'
SELECT 'BLUE, BOB', '123-45-6789' UNION
SELECT 'WHITE, MARY', '000-00-0000'.............
No, you would just need this:
SELECT t.name, t.SSN, f.FEE
FROM @Names t CROSS JOIN (SELECT 25.00 AS FEE UNION
SELECT 50.00 UNION
SELECT 75.00) f
ORDER BY t.name
Where you have to replace the @Names table with your real table.
SELECT t.name, t.SSN, f.FEE
FROM @Names t CROSS JOIN (SELECT 25.00 AS FEE UNION
SELECT 50.00 UNION
SELECT 75.00) f
ORDER BY t.name
Where you have to replace the @Names table with your real table.
Exactly. I used a common table expression with UNION just to get some sample data. Simply use your real table and criteria that gets you to the 100+ names you want.
You already have the Names table to pull names from - try the code I put up (alter it a little for your field names, etc). I just tried it in a SQL Server 2012 on my laptop and it produced the required triplets for a thousand names in less than a second. It really isn't going to take a long time!
select distinct name, ssn
from oldTable