0

I have a table with this structure:

username varchar(35)
startdate datetime
enddate datetime
saledate datetime
packageid int 

for each username there are 5 packages, so one username has 5 records in the table, and the only field that changes is packageid. Now I have been asked to insert for all the users (around 4200) another package in the table, so the statement should be like this:

insert into subscription(username,name,startdate,enddate,saledate,pkgid)
values (@username,@name,@startdate,@enddate,@saledate,41)

41 is the value of the package field

I have included this statement in a procedure, but my problem is in how to access the username field (it logically should access a distinct(username), loop this field until there are no more users left,and in each cycle execute the procedure). The id column of the table is of no help, because the packages at the beginning were not added in group (so I cannot set the id=id+5). Any idea?

7
  • So you want to take the existing unique values of 'username' and simply add new rows with 'pkgid' = 41? If that's the case just instead of using the VALUES clause, just replace it with a SELECT DISTINCT statement from your table.
    – Queue Mann
    Commented May 21, 2015 at 12:55
  • @QueueMann i have already tried, but the problem is that it only adds the data from the last row
    – Ange1
    Commented May 21, 2015 at 12:56
  • But aren't you selecting from subscription to insert into subscription? You just want distinct usernames.
    – Queue Mann
    Commented May 21, 2015 at 12:59
  • when i try with insert select, it inserts the row 5 times
    – Ange1
    Commented May 21, 2015 at 13:06
  • 1
    @Thronk you are right about programming background. Redesigning the table or database is out of question (It was the first thing i asked to my boss).
    – Ange1
    Commented May 21, 2015 at 13:51

2 Answers 2

4

No loops are needed in this (please!).

You should be doing an INSERT...SELECT.

Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted.

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
  username,
  name,
  startdate,
  enddate,
  saledate,
  41
FROM subscription;

If there is a specific startdate and enddate you want to use for all users, then you'll need to set them as variables.

DECLARE @startdate DATETIME;
DECLARE @enddate DATETIME;

SET @startdate = '2015-05-21 10:00:00.000';
SET @enddate = '2015-06-21 00:00:00.000';

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
   username,
   name,
   @startdate,
   @enddate,
   NULL,
   41
FROM subscription;
9
  • @name is another varchar(35) field. the select is taken from the same table where i have to insert data. And something: Why i SHOULD NOT use loops?
    – Ange1
    Commented May 21, 2015 at 13:00
  • SQL tends to work best as a set-based language. Executing one statement for 4200 rows is higly likely to be more efficient than executing 4200 statements for one row Commented May 21, 2015 at 13:02
  • 1
    That might be because the other columns outside of username may not be DISTINCT enough to generate one row per username. So you might have to replace the other columns in the SELECT list to constants.
    – Queue Mann
    Commented May 21, 2015 at 13:08
  • 1
    @ypercube i understood where is the problem... in 37000 rows, there are times where startdate and enddate are different for the same user. I am trying with INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT DISTINCT username, max(name), max(startdate), max(enddate), max(saledate), 41 FROM subscription group by username
    – Ange1
    Commented May 21, 2015 at 13:17
  • 2
    You don't need the distinct in this case., you are using group by. If you are happy with the values inserted (startdate, enddate), your query will work ok, inserting one row only per username. The values though (name, enddate, startdate, saledate) may not make much sense, being gathered from different rows. Commented May 21, 2015 at 13:22
1

Based on the answer of Mark, I was able to define the exact query as it follows:

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT
 username,
max(name),
max(startdate),
max(enddate),
max(saledate),
41

FROM subscription
group by username
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.