insert data into sql table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert data into sql table

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-07-26 : 17:24:11

Hi All,
I have a table with the fields :
MeterID
HourEnd
I have the different MeterIDs, but for every MeterID, the HourEnd value is 1. In all, there are 14336 values of MeterID in the table. I want that for each MeterID, HourEnd values must be from 1 to 24. MeterID is not a primary key.
eg. i want records to appear like this for every MeterID:
MeterID HourEnd
5 1
5 2
5 3
5 4
5 5 and so on till 24.
Is there anyway by sql code i can achieve this?

Thanks in ancticipation,
Mavericky

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 17:47:55

You can use row_number function if you are on SQL 2005 or higher:

;with cte as
(
	select *,ROW_NUMBER() over (PARTITION by MeterId order by (select null)) as NewHourEnd
	from YourTable
)
update cte set HourEnd = 1+NewHourEnd%24;

mavericky
Posting Yak Master

117 Posts

Posted - 2011-07-26 : 18:10:47

@sunitabeck - Thanks for your reply but this only updates the 14336 records with the value of HourEnd. Can I get something like this - values for every MeterID from 1 to 24. Each MeterID has one row corresponding to it. After that each MeterID will have 24 rows corresponding to itself, ranging from 1 to 24. So the total number of records will also rise.(there will be in all 14336*24 = 344064 records)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 19:47:20

Ah, sorry about that - I did not read your original post carefully enough. See if this will work for you? I am using #tmp table, but you will need to replace the #tmp with your table name

-- TEST DATA
create table #tmp (MeterId int, HourEnd int);
insert into #tmp  
	select 101,1 union all
	select 102,1 union all
	select 201,1;

-- SCRIPT
;with N(n) as
( select 1 union all select n+1 from N where n < 23 ),
cte as
(
	select y.MeterId,ROW_NUMBER() over (PARTITION by MeterId order by (select null)) as NewHourEnd
	from #tmp y cross join N
)
insert into #tmp select MeterId,NewHourEnd+1 from cte;

-- TEST THE RESULTS
select * from #tmp order by MeterId, HourEnd;

-- CLEANUP
drop table #tmp;

mavericky
Posting Yak Master

117 Posts

Posted - 2011-07-27 : 18:10:05

@sunitabeck. this is correct but i have 15000 ids. ie. 101, 102, till 15000. Is there any other way than manually listing them?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 20:29:31

quote:
Originally posted by mavericky

@sunitabeck. this is correct but i have 15000 ids. ie. 101, 102, till 15000. Is there any other way than manually listing them?

You don't need to type in or manually list the 15,000 MeterIDs, since you already have those in your table.

The script I posted has four parts.

Part 1: -- TEST DATA
This section is just for testing. Since I don't have access to your table, I had to create one with sample data. So on your server, you don't need to do this part.

Part 2: -- SCRIPT
This is the actual script you want to use. Since I wrote the script for the test data, you will need to make the required changes to make the script work with your data. I think the only change you will need to do is replace the table name, i.e., replace #tmp with your actual table name in this statement:

from #tmp y cross join N

Part 3: -- TEST THE RESULTS
This portion is just to see what the script did. No need for you to run this.

Part 4: -- CLEANUP
Here, I am deleting the temporary table that I created. In your case you DEFINITELY should NOT delete your actual table.

I hope that makes sense. Do three things:

1. Copy Part 1 of the script and run it. Then, see what is in the table using "select * from #tmp". This data should look similar to what you have in your actual tables now.

2. Copy part 2 and 3 and run it. Now the data should be similar to what you actually want to get.

3. If you are satisfied, copy part 2 and change the table name (#tmp to your actual table name) and run it and you should be good.

mavericky
Posting Yak Master

117 Posts

Posted - 2011-07-28 : 23:46:56

Thanks a lot sunitabeck!!! This was really helpful. My problem is solved.
APPRECIATED!!!!!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 06:56:55

ah! you are quite welcome, glad it worked out finally.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources