SQL 2008 Record Creation


Code  Desc
11      Car
12      Boat
13      Bike
22      House
25      Garage
46      Plane    

I need to create 4 new records for each 1 record from above table and write to a new table called tableb.
(code will be 3 char and desc  would be 50 in new table)
(the same 4 characters (1,2,3,4) will be added to the end of the current code creating new char code)
(the same 4 words (Check,Adjust,Repair,Replace) will be added to the end of the current desc creating new desc

New table should look like below:


111   Car Check
112   Car Adjust
113   Car Repair
114   Car Replace
121   Boat Check
122   Boat Adjust
123   Boat Repair
124   Boat Replace
131   Bike Check
132   Bike Adjust
133   Bike Repair
134   Bike Replace
221   House Check
222   House Adjust
223   House Repair
224   House Replace

thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

how big is tableA? can you provide all values in Excel for me?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not quite getting what you're asking for, but copy-paste the below T-SQL into your SSMS, execute to verify it answers your question, then modify to fit your needs
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL

CREATE TABLE #TableA (code int, description varchar(50))
INSERT INTO #TableA (code, description) 
VALUES (11, 'Car'), (12, 'Boat'), (13, 'Bike'), (22, 'House'), (25, 'Garage'), (46, 'Plane')   

CREATE TABLE #TableB (code int, description varchar(50))
INSERT INTO #TableB (code, description) 
VALUES (1, 'Check'), (2, 'Adjust'), (3, 'Repair'), (4, 'Replace')

SELECT (a.code * 10) + b.code, a.description + ' ' + b.description
FROM #TableA a
   CROSS JOIN #TableB b
ORDER BY (a.code * 10) + b.code

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
create a table called B:
id      descr
1      Check              
2      Adjust              
3      Repair              
4      Replace            

this should produce the results you want then insert into a new table.

SELECT A.[Code]+B.id AS ItemCode
      ,RTRIM(A.[Desc])+' '+ RTRIM(B.descr) AS ItemDescription
  FROM [A]


  order by A.[Code],B.id

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

thayduckProgrammer AnalystAuthor Commented:
thanks, not needed I went ahead and provided sql code but looks like Jim beat me to it. He has a more complete solution. The answer is to use Cross Join
thayduckProgrammer AnalystAuthor Commented:
CREATE TABLE #TableA (code int, description varchar(50))
INSERT INTO #TableA (code, description)
VALUES (11, 'Car'), (12, 'Boat'), (13, 'Bike'), (22, 'House'), (25, 'Garage'), (46, 'Plane')  

So, I would have to put all 75 values here ?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> .. for each 1 record from above table
Assuming it's already a table than use that.  I just used #TableA and populated it that way so I can test it in my SSMS, as I'm not connected to your data source so I couldn't be certain that it works.  

If it's not already in a table, but only in your Excel doc, then import that into a table.
note Jim's solution produces a final table having an int type for the codem hence the X 10 in:  (a.code * 10) + b.code

use: a.code + b.code

if you're working with char concatenation
thayduckProgrammer AnalystAuthor Commented:
drop table #tableb

CREATE TABLE #TableB (code int, description varchar(7))
INSERT INTO #TableB (code, description)
VALUES (1, 'Check'), (2, 'Adjust'), (3, 'Repair'), (4, 'Replace')

select *   from #tableb

SELECT (a.repcd * 10) + b.code, rtrim(a.[repcd-name]) + ' ' + rtrim(b.description)
FROM dbo.repaircodenew a
   CROSS JOIN #TableB b
ORDER BY (a.repcd * 10) + b.code

This is working except for small issue that I tried to fix with  'rtrim' , but does not help.
Is there a way to get rid of all the spaces between repcd-name and b.description in most of records ?

(No column name)      (No column name)
154      Steering Replace
161      Suspension Check
162      Suspension Adjust
163      Suspension Repair
164      Suspension Replace
171      Tires/Tubes Check
172      Tires/Tubes Adjust
173      Tires/Tubes Repair
174      Tires/Tubes Replace
181      Wheels/Rims/Hubs           Check
182      Wheels/Rims/Hubs           Adjust
183      Wheels/Rims/Hubs           Repair
184      Wheels/Rims/Hubs           Replace
191      Automatic Chassis Lube Check
192      Automatic Chassis Lube Adjust
193      Automatic Chassis Lube Repair
194      Automatic Chassis Lube Replace
211      Front Axle Driven Check
212      Front Axle Driven Adjust
213      Front Axle Driven Repair
214      Front Axle Driven Replace
221      Rear Axle Driven          Check
222      Rear Axle Driven          Adjust
223      Rear Axle Driven          Repair
224      Rear Axle Driven          Replace
231      Clutch                     Check
232      Clutch                     Adjust
233      Clutch                     Repair
234      Clutch                     Replace
241      Drive Shafts              Check
242      Drive Shafts              Adjust
243      Drive Shafts              Repair
244      Drive Shafts              Replace
251      Transfer Case              Check
252      Transfer Case              Adjust
253      Transfer Case              Repair
254      Transfer Case              Replace
261      Transmission Main         Check
262      Transmission Main         Adjust
263      Transmission Main         Repair
264      Transmission Main         Replace
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>rtrim(a.[repcd-name]) + ' ' + rtrim(b.description)
Use LTRIM and RTRIM.  Last I recall there's not a TRIM that covers both.
ltrim(rtrim(a.[repcd-name])) + ' ' + ltrim(rtrim(b.description))

Open in new window

for a small dimension table like this I would just clean up in excel. Here's the final version.
thayduckProgrammer AnalystAuthor Commented:
Thanks for the Help.
no assist points for me? :(
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
BTW, did the rtrim function work for you? it didn't remove the trailing spaces for me? usually it works but I think there's something odd about the data you provided.  I think it may contain Non-ascii characters that are non-visible so it looks like spaces but in reality it's not.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.