SQL Server - Multiple insert

grahamor

Hi,

I have a spreadsheet of about 600 values that need to be inserted into a table.
The insertion can only be done through the query analyser so i cant use Enterprise manager to create
a dts package (which i would normally do)

Im wondering if i can put all these values into an array and jog through them and insert each one.

Any ideas would be great.

Evil Phil

I'm assuming (probably incorrectly) that you're writing a sql script to insert them because you've said you have to use query analyser. Is this true? If so I'm not sure how you're going to use an array. Sounds like a cut and paste job to me. If so use a good text editor (or even a poor one) to insert the required SQL statements and then run that from QA.

bonkey

Use the spreadsheet functionality to build a SQL INSERT for each row.

If its Excel, for a 2-column insert you'd have something like the following in C1:

="INSERT into MyTable Values ('" & A1 & "', " & B1 & ")"

Then copy-fill this down for your 600 rows, so the row value is auto-incrememnted in the formula for you.

Then copy the 600 rows and paste the results into Query Analyzer.

Note that I've assumed column A contains string data (hence the ' around it) where B contains numeric.

This is the easiest (IMHO) one-off solution. If you need to automate, or do something more clever, then obviously you need to code a solution. Again, if its in Excel, some VBA would be your best bet.

zdragon

ok guys verry funny:)
so I think the better way will be to have data in CSV format, or any other structure like XML ..
then use a simple SQL guery to read this CSV file and insert it in your table..
don't mess with copy/paste and text editors.

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

for more check with MSDN:http://msdn2.microsoft.com/en-us/library/ms188365.aspx

grahamor

i knew there was a way to automate this.

Thanks for all the replies though.

Ginger

Just use BCP . . . and change the file to csv. Will be in a jiffy