insert multiple rows 1

[johnaregan]

How would I insert multiple rows into a table just using SQL? I have a list of usernames and passwords that I want to insert in one go.

thks in advance

 

[tlbroadbent]

What is the format of the list of names and passwords? There are many ways to insert multiple rows just using SQL. You can use the BULK INSERT command if the names and passwords are listed in a comma sparated text file.

BULK INSERT UsersTbl
FROM 'f:\orders\lineitem.tbl'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

See the BULK INSERT topic in SQL BOL.

You can also run several consecutive INSERT statements in one batch.

INSERT UsersTbl (UserName, Password) Values ('bob','secret')
INSERT UsersTbl (UserName, Password) Values ('mary','secret')
INSERT UsersTbl (UserName, Password) Values ('sally','secret')
INSERT UsersTbl (UserName, Password) Values ('chuck','secret')
INSERT UsersTbl (UserName, Password) Values ('lynn','secret')

Hope this helps. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[johnaregan]

Thanks for the reply.
I would like to just copy and paste into the query window a list like
('name1', 'pwd1'
'name2', 'pwd2',.....)

 

[tlbroadbent]

Sorry, you can't just copy and paste into a Query Window. If I had a list like that, I'd edit it in Query Analyzer, a text editor or word processor. Create a separate line for each pair or names and passowrds. Then add the SQL INSERT statements and appropriate syntax. Run the resultant statements in Query Analyzer.

INSERT UsersTbl (UserName, Password) Values ('name1','pass1')
INSERT UsersTbl (UserName, Password) Values ('name2','pass2')
.
.
. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost