SQL 2003 - Insert Help

[Vogel515]

Hello - I am trying to do a bulk insert from a CSV file. The DST function in SQL Studio Express kind of sucks, so I am looking for a way to do this using the INSERT function.

In MySQL its easy:

INSERT INTO
(col1,col2, etc)
VALUES
(val1,val2,etc),
(val1,val2,etc),
(val1,val2,etc),
(val1,val2,etc),
(val1,val2,etc),
(val1,val2,etc)

However, in SQL it does not except multiple lines of values. Is there anyway to accomplish this without using the bulk insert feature? (I'm awaiting permissions to use it, its currently blocked for my use, but now I'm sick of waiting.)

 

[presidentender]

Write a script to do what you can over and over: run a single insert statement per query, and run a bajillion queries. It sucks for performance, but it's easy to do.

 

[Vogel515]

There are about 30,000 inserts to do. It's a test server so I don't mind taxing it.

Is there a good way to organize the scripts to run themselves?

I could easily just create the strings in excel, but I don't want to have to cut and paste the statements 30,000 times.

 

[JACKDRUID]

put all values into another table name tblValue

INSERT INTO
(col1,col2, etc)
VALUES
(SELECT val1,val2,etc FROM tblValue)


another way is to use a cursor

declare @val1 nvarchar(50)
declare @val2 nvarchar(50)
declare @etc nvarchar(50)
declare x as cursor for
( select val1,val2, etc FROM tblValue)
Open x
fetch next from x into @val1,@val2,@etc
while @@fetch_status=0
begin
INSERT INTO (col1,col2,etc) Values (@val1,@val2,@etc)
fetch next from x into @val1,@val2,@etc
end
close x
deallocate x

 

[Vogel515]

Hello Jack - thanks for the response

How do I get the CSV into a table? Doing the insert would be very easy if the values were in the database.

Is there a way to view the CSV document as a table?

 

[KLin]

Originally posted by: Vogel515
Hello Jack - thanks for the response

How do I get the CSV into a table? Doing the insert would be very easy if the values were in the database.

Is there a way to view the CSV document as a table?

Do the following from SQL Management Studio:

1. Right click on database name
2. Click Tasks, then import data
3. Choose flat file for the data source(put in filename, etc.)
4. choose the SQL Server as the destination
5. etc. etc. etc. you're done

http://blogs.microsoft.co.il/b...ert-multiple-rows.aspx

Looks like multiple values with one insert statement is coming in SQL 2008. :thumbsup:

 

[Vogel515]

Hello Klin - thanks for your response

I do not have the option of importing data through that means. I am using MS SQL Server Management Express, and it seems to be very limited in its functionality.

I did however find a solution.

presidentender - thank you very much, I am not sure what I was thinking. I ended up creating an excel formula that created an insert string for each row. I then just pasted all 30,000 in there at once and had the result I wanted.

Like I said, I'm not sure what I was thinking, at first I was trying to union the inserts and some other crap.... brain fart I guess.

Thanks again for all your help.

 

[JACKDRUID]

you may also use bcp command line utility