General discussion

Locked

Microsoft SQL2000 Databases

By shield509 ·
I have a database created in MS SQL2000 that will require the entry of 48,000 ID numbers. The numbers start at 1B0001 thru 1B8000.Then 2B0001 thru 2B8000 upto and including 6B8000. Is there any method that would allow me to create the 1B series and the others similar to the method I used in Excel to list the ID numbers on a spreadsheet? 1B0001, 1B0002 then highlight the two boxes and drag the curser down the column until I reached the 1B8000 box and have the numbers listed in series automatically? It takes on adverage and hour to enter 500 ID numbers when I go into the SQL database and add the ID number.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Microsoft SQL2000 Databases

by Joseph Moore In reply to Microsoft SQL2000 Databas ...

Ok, I did this in about 5 minutes (and that counted getting Excel to co-operate correctly!!)

First, let's say your database is called BOOKS, and it is in table AUTHORS you want to create your 48000 records with these 1B, 2B and 6B fields.

Ok. Make a blank spreadsheet. In Column A Row 1 put in:
insert into AUTHORS (NUMBERCOL) values

In Column B, Row1, put in:
(1B0001

And in Column C, Row1 put in:
)

Now, copy/paste Column A Row1 cell (the Insert command) and paste them for as many thousands of records you need for the 1B line. Next, do the copy/paste for Column B Row1 cell but do it as a Fill Series so you get (1B0002 in Row2, (1B0003 in Row3, etc.
And lastly, copy/paste Column C Row1, the closed paranthesis, into every row.

Then, just SAVE AS the whole thing as a UNICODE .TXT file. For example, save this first one as 1B.TXT

Ok, now open 1B.TXT and it will look like this:
insert into AUTHORS (NUMBERCOL) values (1B0001 )
insert into AUTHORS (NUMBERCOL) values (1B0002 )
insert into AUTHORS (NUMBERCOL) values (1B0003 )
insert into AUTHORS (NUMBERCOL) values (1B0004 )
insert into AUTHORS (NUMBERCOL) values (1B0005 )
insert into AUTHORS (NUMBERCOL) values (1B0006 )
etc.


All you then need to do in Notepad is do a Find/Replace, where you searc for " )" and replace it with just ")" (without the quotation marks!)

Doing that, you get:

insert into AUTHORS (NUMBERCOL) values (1B0001)
insert into AUTHORS (NUMBERCOL) values (1B0002)
insert into AUTHORS (NUMBERCOL) values (1B0003)
insert into AUTHORS (NUMBERCOL) values (1B0004)
insert into AUTHORS (NUMBERCOL) values (1B0005)
insert into AUTHORS (NUMBERCOL) values (1B0006)
etc.

There you go.

Collapse -

Microsoft SQL2000 Databases

by Joseph Moore In reply to Microsoft SQL2000 Databas ...

Now just open Query Analyzier, select your database in the drop-down list, then File -> Open and select the 1B.TXT file.

After that, click the green arrow button to run this file!

It will create records in the AUTHORS table, where the NUMBERCOL field = the 1B#### entry for each record. If you have 10,000 1B records in the 1B.TXT file, this will make 10,000 records in your SQL Server database with the correct 1B info per record!

hope this helps

Collapse -

Microsoft SQL2000 Databases

by shield509 In reply to Microsoft SQL2000 Databas ...

This solution saved me hours of tedious work. A real life saver. Sharing knowledge is such a wonderful thing. Thanks.

Collapse -

Microsoft SQL2000 Databases

by shield509 In reply to Microsoft SQL2000 Databas ...

This question was closed by the author

Back to IT Employment Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums