Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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
0
JElster
Asked:
JElster
  • 4
  • 3
  • 3
  • +2
3 Solutions
 
LIONKINGCommented:
Will a

Select NAME, SSN FROM Names
UNION ALL
Select NAME, SSN FROM Names
UNION ALL
Select NAME, SSN FROM Names


Work?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Insert into newTable (name, ssn)
select distinct name, ssn
from oldTable
0
 
DcpKingCommented:
run this to see.

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, Amount

Open in new window


hth

Mike
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
JElsterAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
I would not use a LOOP. Instead, create a derived table or common table with the fees you want, then CROSS JOIN or other such technique to apply each row of fees to each row of names.

For example:
;WITH table_of_names(NAME, SSN) AS (
    SELECT 'BROWN, JOHN', '123-45-6789' UNION
	SELECT 'GREEN, SUE', '000-00-0000'
)
--INSERT INTO other_table(NAME, SSN, FEE)
SELECT n.NAME, n.SSN, f.FEE
FROM table_of_names n
CROSS JOIN (
   SELECT 25.00 AS FEE UNION
   SELECT 50.00 UNION
   SELECT 75.00
) f
;

Open in new window


Once you are ready, uncomment the INSERT and correct table and column names.
0
 
JElsterAuthor Commented:
How long is that statement going to be for hundreds of names?
0
 
DcpKingCommented:
"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.
0
 
JElsterAuthor Commented:
How long is that statement going to be...?  I have  987 names
0
 
LIONKINGCommented:
It doesn't matter how many names you have, if you follow mwvisa1's code you probably don't need the derived table and you'd end up with something like:

DECLARE @Names TABLE(
name VARCHAR(200) NOT NULL,
SSN VARCHAR(11) NOT NULL)

INSERT INTO @Names
SELECT 'BROWN, JOHN', '123-45-6789' UNION
SELECT 'GREEN, SUE', '000-00-0000'


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

There you just need to replace the @Names table with your real Names table.
0
 
JElsterAuthor Commented:
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'.........................................................................
0
 
LIONKINGCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
DcpKingCommented:
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!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now