Create table, insert data ... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Create table, insert data ...

Author

Topic

kelvin.loh
Starting Member

3 Posts

Posted - 2003-05-09 : 02:58:01

Hi,

I do need the help from all of you. Sure hope that i can get some help here.

My table is designed: -
DO_NO_ALPHA char (1),
START_DO_RANGE int,
END_DO_RANGE int,

And the records are stored as such: -
DO_NO_ALPHA START_DO_RANGE END_DO_RANGE
----------- -------------- ------------
X 1 350

I have to create a table, which will look like this after reading the records above: -
DO_NO Status
------ ------
X00001 N
X00002 N
...... ...
... .......
...........
X00350 N

Status will always be 'N'

How should i write the Store procedure? Like say, if i want to write out a Store procedure that create a table temporary. After processing, the table will be dropped off.

Will it be the same if i would to write a procedure that creates the table permanently?
What will be the differences?

Please enlighten me.

Thank you and best regards.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-09 : 11:00:43

You will have to do this in a loop from start to end range or create a table with all the numbers in it and join to that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mtomeo
Starting Member

30 Posts

Posted - 2003-05-09 : 12:20:34

This should do the trick....maybe someone can come up w/a cleaner, SELECT INTO version.


create table #MyTbl 
	(DO_NO_ALPHA char (1), 
	 START_DO_RANGE int, 
	 END_DO_RANGE int)
Insert INTO #MyTbl (DO_NO_ALPHA, START_DO_RANGE,  END_DO_RANGE) values ('X', 1, 350)

Select * from #MyTbl

create table #NewTbl 
	(DO_NO VarChar (6), 
	 Status VarChar(1))

DECLARE @i INT
DECLARE @v VarChar(6)
DECLARE @End INT
SET @i = (SELECT START_DO_RANGE FROM #MyTbl)
SET @End = (SELECT END_DO_RANGE FROM #MyTbl)

WHILE @i <= @End
BEGIN
  SET @v = REPLICATE('0', 5 - DATALENGTH(Convert(varchar(6),@i))) + Convert(varchar(6),@i)
  INSERT INTO #NewTbl SELECT Convert(varchar(1),DO_NO_ALPHA) + @v, 'N' FROM #MyTbl
  SET @i =  @i + 1
END

Select * from #NewTbl
go

drop table #MyTbl
drop table #NewTbl
go

shifis
Posting Yak Master

157 Posts

Posted - 2003-05-09 : 12:36:17

This will only works if we have one row in the table #MyTbl if we add another row we will have problems withe the lines

SET @i = (SELECT START_DO_RANGE FROM #MyTbl)
SET @End = (SELECT END_DO_RANGE FROM #MyTbl)

because they will return more than one value.
There is a way to read one by one the row of the table #MyTbl or we have to uses cursors?

mtomeo
Starting Member

30 Posts

Posted - 2003-05-09 : 13:02:48

I assumed there would only be one record. I would lean towards a cursur...the whole process isn't really that expensive. Of course, I completely ignored the Stored Proc question. You could easily put this in an SP.



create table #MyTbl 
	(DO_NO_ALPHA char (1), 
	 START_DO_RANGE int, 
	 END_DO_RANGE int)
Insert INTO #MyTbl (DO_NO_ALPHA, START_DO_RANGE,  END_DO_RANGE) values ('X', 1, 350)
Insert INTO #MyTbl (DO_NO_ALPHA, START_DO_RANGE,  END_DO_RANGE) values ('Y', 1, 200)
go

create table #NewTbl 
	(DO_NO VarChar (6), 
	 Status VarChar(1))
go

DECLARE cIns CURSOR
FOR
   SELECT DO_NO_ALPHA, START_DO_RANGE, END_DO_RANGE 
   FROM #MyTbl
OPEN cIns
DECLARE @i INT
DECLARE @v VarChar(6)
DECLARE @End INT
DECLARE @DO_NO_ALPHA VarChar(1)
FETCH NEXT FROM cIns INTO @DO_NO_ALPHA, @i, @End
WHILE @@FETCH_STATUS = 0
BEGIN
  WHILE @i <= @End
  BEGIN
    SET @v = REPLICATE('0', 5 - DATALENGTH(Convert(varchar(6),@i))) + Convert(varchar(6),@i)
    INSERT INTO #NewTbl SELECT Convert(varchar(1),@DO_NO_ALPHA) + @v, 'N'
    SET @i =  @i + 1
  END
  FETCH NEXT FROM cIns INTO @DO_NO_ALPHA, @i, @End
END
CLOSE cIns
DEALLOCATE cIns

Select * from #NewTbl
Order by DO_NO
go

drop table #MyTbl
drop table #NewTbl
go

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-09 : 17:54:22

Use a tally table ... create any table with numbers from 1-1000 in it. call this "Numbers" and the column with the number "i".

select do_no_alpha + right('00000' + convert(varchar, start_do_range + i - 1),5) as DO_NO, 'N' as Status
FROM
YourTable
INNER JOIN
Numbers
ON
Numbers.I <= (End_Do_Range - Start_Do_Range)

Voila !! couldn't be easier. I guarantee this is about 100 times faster than the cursor, and obviously works for any # of rows in the table.

Just make sure the tally table has enough numbers in it to handle all possibilities.

search this site for "tally table" for more info on tally tables. very useful. elimintes the need for almost all cursor solutions in the world of SQL ....

(warning: above may need some tweaking; not tested)

- Jeff

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-09 : 19:18:01

Jeff,

Nice work as usually

But, it only return 349 rows ( the last row is x00349 N instead of x00350 N)

Jung

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-09 : 19:20:43

Hopefully, the solution to this problem is obvious !

but just in case:

select do_no_alpha + right('00000' + convert(varchar, start_do_range + i - 1),5) as DO_NO, 'N' as Status
FROM
YourTable
INNER JOIN
Numbers
ON
Numbers.I <= (End_Do_Range - Start_Do_Range) +1

(note that this is another great CROSS JOIN solution -- though i did code it in this case as an inner join...!!)

- Jeff

Edited by - jsmith8858 on 05/09/2003 19:23:41

kelvin.loh
Starting Member

3 Posts

Posted - 2003-05-12 : 04:10:16

Hi,

A very big thank you for the help generated.

Thank you for all the replies.

Have a great and wanderful day ahead.

Best regards.

Kelvin Loh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources