July 1, 2010 at 3:08 pm
I have a table with id data, and 7+ columns of info data.
I need to put this (with a little conversation) into a different table with id data and 1 (one) column of info data.
i.e.
Field 1 = User_ID
Field 2 = Prg_Hrs_Monday
Field 3 = Prj_Hrs_Tuesday
...
Field 8 = Prj_Hrs_Sunday
... Other fields with data
Makes it easy, one row for each User_ID,
But!
I need to come up with a Stored Procedure (what I am familiar with) to insert this data into a different table (with a little massage on different other not shown fields).
i.e.
Field 1 = User_ID
Field 2 = Weekday
Field 3 = Prj_Hrs
... Other fields with data
Yes I know this is not normalized but rather going the opposite direction.
With this I will be (I hope) doing a bulk insert into an OpenSource/Linked Server.
I'm not as familiar writing code in SQL but, with help from you guys, I'm getting better. In VBA I could use a 'Do While not EOF Loop' for each record and within that loop have a 'For (intX = 1 to 7) Next' to handle each of the seven days. Tedious but easy and could/will end up with tons of records.
We use Access as our front end so this is where I can use VBA, but I'm moving the data from the SQL server to the PC/Workstation and back to the SQL server (then on the to MainFrame server). That may, because of my limited experience, be the way I end up doing it.
I would appreciate any help or suggestions you can give me.
Thank you,
July 1, 2010 at 3:15 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 4:21 pm
Assuming you're using SQL2000 then the following approach might help:
SELECT
User_ID as Field1,
'Monday' as Weekday,
Prg_Hrs_Monday=Prj_Hrs
FROM YourTable
UNION ALL
SELECT
User_ID as Field1,
'Tuesday' as Weekday,
Prg_Hrs_Tuesday=Prj_Hrs
FROM YourTable
...
If you're using SQL 2005 or above, you might want to look into UNPIVOT.
For a more detailed answer please follow Gails advice and post some ready to use data so we have something to test our solutions against and to better understand your requirements.
July 6, 2010 at 11:48 am
If I get waht you are looking for 7 new records from each previous record, this should work in one step on the server.
insert into newtable(field1,field2,field3,other fields)
select field1,'Monday' DOW, Field2 Hours,Other fields from oldtable union
select field1,'Tuesday' DOW, Field3 Hours,Other fields from oldtable union
select field1,'Wednesday' DOW, Field4 Hours,Other fields from oldtable union
select field1,'Thursday' DOW, Field5 Hours,Other fields from oldtable union
select field1,'Friday' DOW, Field6 Hours,Other fields from oldtable union
select field1,'Saturday' DOW, Field7 Hours,Other fields from oldtable union
select field1,'Sunday' DOW, Field8 Hours,Other fields from oldtable
July 6, 2010 at 1:35 pm
Great ideas but... there's always a but.
I should have given more info. The individual unions for a single insert won't work because there may be multiple lines per user per day. Sorry.
I have used unions before and I think this may work if I can figure out how to use a bulk insert using an Openquery on a DB9 (IBM iSeries) server.
That would solve the problem. A little more detail (still paired down):
Tbl_Raw_Data
Field 1 = User_ID nvarchar(6)
Field 2 = Prg_Hrs_Monday Real
Field 3 = Prj_Hrs_Tuesday Real
...
Field 8 = Prj_Hrs_Sunday Real
Field 9 = Wrk_Type nvarchar(3)
... Other fields with data
Makes it easy, one row for each User_ID and unique Wrk_Type, i.e. any user can have multiple rows for the same day if the type is different.
I need to insert this into a DB9 table that is one row per user, per day, per type; i.e.
tbl_DB9
Field 1 = User_ID nvarchar(6)
Field 2 = Weekday int
Field 3 = Prj_Hrs real
Field 4 = Wrk_Type nvarchar(3)
... Other fields with data
There could (easily) be 1,000 plus records to insert.
I can:
A) Using VBA build the insert statements with a Pass-Though query, that's a lot of insert statements and like I said I'd move the data from a SQL Server to a PC and back again.
B) Using T-SQL build the insert statements which I have no idea at all how to do and I'd be using the dreaded RBAR (row by agonizing row) approach. Records Sets, Row Sets, etc??? But a Do while Loop would work if I know how to use the Record/Row sets.
C) This might be two parts:
C1) Using T-SQL build a bulk insert to a table on the SQL Server
C2) Using T-SQL build a bulk insert from that table to the Linked Server (iSeries using DB9).
C is what I'm attempting right now. Row by row, at least at this point, is a little beyond me. Though, I have have to brave method A or B if i can't get C working.
Thanks again for your help,
It really is appreciated.
July 6, 2010 at 1:46 pm
July 6, 2010 at 1:55 pm
@ Ken
Please provide ready to use table def and sample data together with expected result as described in the first link in my signature.
What exactly is the difference of the concept you posted vs. the one from the post above yours? :ermm:
July 6, 2010 at 2:00 pm
I believe both solutions are identical and work perfectly for the request.
Only issue I can see here is that Ken@Work did not bother to read and understand either of them.
_____________
Code for TallyGenerator
July 6, 2010 at 2:02 pm
July 8, 2010 at 6:32 am
Actually I read it.
I thought I posted the table definition above though probably not well enough.
No I didn't post sample data as I thought my question was more general nature.
Thank you for your advise,
July 8, 2010 at 6:41 am
I like the idea of Unions and have used them before, though I've never used them in inserts.
I'll be testing these shortly; it really looks like a good idea and should work.
Thanks guys.
Never expected to get chastised over what turned out to be a simple answer.
Forgive my inexperience, perhaps I'm on the wrong forum/site.
Thanks again for the help,
I appreciate it.
July 8, 2010 at 7:36 am
Ken@Work (7/8/2010)
I like the idea of Unions and have used them before, though I've never used them in inserts.I'll be testing these shortly; it really looks like a good idea and should work.
Thanks guys.
Never expected to get chastised over what turned out to be a simple answer.
Forgive my inexperience, perhaps I'm on the wrong forum/site.
Thanks again for the help,
I appreciate it.
I'm sure the idea wasn't to chastise you, but experience has taught most of us that while loops are a great deal less efficient than set based solutions for most problems. Which is why you were asked to supply table definitions.
In answer to your origional query, yes, T-SQL does have while loops.
http://msdn.microsoft.com/en-us/library/aa260676(SQL.80).aspx
For example (knocked up on my phone, so not validated): -
DECLARE @counter INT
SET @counter = 99
WHILE @counter > 11
BEGIN
PRINT CAST(@counter AS CHAR(2)) + ' bottles of beer on the wall'
PRINT CAST(@counter AS CHAR(2)) + ' bottles of beer'
SET @counter = @counter - 1
PRINT 'Take one down and pass it around, ' + CAST(@counter AS CHAR(2)) +
' bottles of beer on the wall'
END
WHILE @counter > 2
BEGIN
PRINT CAST(@counter AS CHAR(1)) + ' bottles of beer on the wall'
PRINT CAST(@counter AS CHAR(1)) + ' bottles of beer'
SET @counter = @counter - 1
PRINT 'Take one down and pass it around, ' + CAST(@counter AS CHAR(1)) +
' bottles of beer on the wall'
END
PRINT CAST(@counter AS CHAR(1)) + ' bottles of beer on the wall'
PRINT CAST(@counter AS CHAR(1)) + ' bottles of beer'
SET @counter = @counter - 1
PRINT 'Take one down and pass it around, ' + CAST(@counter AS CHAR(1)) +
' bottle of beer on the wall'
PRINT CAST(@counter AS CHAR(1)) + ' bottle of beer on the wall'
PRINT CAST(@counter AS CHAR(1)) + ' bottle of beer'
SET @counter = @counter - 1
PRINT 'Take one down and pass it around, no more bottles of beer on the wall'
July 8, 2010 at 7:47 am
Ken@Work (7/8/2010)
INever expected to get chastised over what turned out to be a simple answer.Forgive my inexperience, perhaps I'm on the wrong forum/site.
Don't mind Sergiy, he's like that to everyone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 7:49 am
You did this on your phone?
That's great! Thanks,
However you are right.
The main reason I was going with a loop is because I could not get the bulk insert to work from SQL server to DB9. I could insert individual lines but never a bulk insert. That's also the reason I didn't use Unions.
I finally got the bulk insert working (yeah!!! Capitalization, among other things, does count), so I'm off and running with the Unions now. Which, you are right, is a much cleaner solution.
I'll save the loop example and look it up on MSDN.
Thanks again, everyone,
Still trying and still learning
July 8, 2010 at 8:17 am
Ken@Work (7/8/2010)
You did this on your phone?That's great! Thanks,
However you are right.
The main reason I was going with a loop is because I could not get the bulk insert to work from SQL server to DB9. I could insert individual lines but never a bulk insert. That's also the reason I didn't use Unions.
I finally got the bulk insert working (yeah!!! Capitalization, among other things, does count), so I'm off and running with the Unions now. Which, you are right, is a much cleaner solution.
I'll save the loop example and look it up on MSDN.
Thanks again, everyone
Yeah, lovely touch keyboard but no SQL Server, should be an app for that. . . 😛
*EDIT*
I've spotted an error in my loop, "counter > 11" should read "counter > 9". Pretty sure "10" has two digits, so won't cast into CHAR(1). . . wonder if it'll fall over or if something else will occur. I look forward to testing if I ever get off this train!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply