Glad to help. This might seem somewhat convoluted, but if you follow the steps one at a time it really won't take all that long. I had to do this for a few thousand zip codes, so that is what my example is going to be. Adjust to what you need accordingly.
So, first you create your table:
CREATE TABLE thezips
(id INT IDENTITY (1,1) PRIMARY KEY, <-- this will increment each record you insert by 1
state VARCHAR (20),
zip VARCHAR(10),
city VARCHAR(150),
county VARCHAR (20)
);
the reason the zip column is VARCHAR is because there might be the extended zip (i.e. 12345-1234).
Now to add all your data:
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07001','Avenel','Middlesex');
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07002','Bayonne','Hudson');
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07003','Bloomfield','Essex');
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07004','Fairfield','Essex');
...and so on...
Instead of writing the INSERT statement over and over, go to MS Excel and make sure your data is in it (i.e. NJ, 07004, Fairfield, Essex), then insert a new column in between each column containing data. Then, starting from the far left column you'll enter the characters and it'll look something like this:
(' NJ ',' 07004 ',' Fairfield ',' Essex ');
IMPORTANT: In Excel if you start your entry with an apostrophe ( ' ), then Excel thinks you're forcing the cell to recognize text instead of numeric and the ' will disappear. So, you need to enter a leading ' for those cells that contain ',' so it'll look like this when you enter '','. The first ' will disappear and it will not carry over when you copy and paste later.
Once you have one row complete, simply copy and paste all the character columns down to match the amount of data you have.
Then copy and paste your data into a text editor (i.e. Notepad), then copy and paste all the data from your text editor into MS Word. If you omit the text editor, Word will think what you're copying is Excel info and that's not what you want.
In Word do a Find/Replace (CTRL + F, then click on the Replace tab). In the "Find what:" field type ^t and leave the "Replace with:" field blank. Click "Replace All". Word will tell you how many records it replaced - click "OK". You should now have data that looks like this:
('NJ','07004','Fairfield','Essex');
Now, in the Find/Replace box, in the "Find what:" field type ^p(' and in the "Replace with:" field type ^p('INSERT INTO zipcodes (state,zip,city,county)^pVALUES^p and click "Replace All". The INSERT statement should correspond to whatever your data is. Click "OK".
Finally, go to the top of the Word doc and make sure that your first statement is correct, then go to the bottom of the Word doc and delete any extraneous code that was inserted due to extra returns. When you're done you will end up with code that looks something like this:
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07001','Avenel','Middlesex');
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07002','Bayonne','Hudson');
INSERT INTO zipcodes (state,zip,city,county)
VALUES
('NJ','07003','Bloomfield','Essex');
Now copy and paste this back into a text editor (Notepad) in order to get rid of any weird, hidden Word characters, then copy and paste the data from the text editor to your SQL editor to execute the code.
Like I said, several steps, but just follow one by one and it'll work out.
Let me know if you have any questions. Good luck.
-Jahn |