SQL Server Programming - INSERT many records with one statement

Author

INSERT many records with one statement

Chris

2005-11-29, 7:05 pm

I'm trying to insert xx identical records with 1 statement.

Basically, I want to avoid the following if I know I want to insert 6
records:
INSERT INTO myTable(Field1,Field1) VALUES(1,2);
INSERT INTO myTable(Field1,Field1) VALUES(1,2);
INSERT INTO myTable(Field1,Field1) VALUES(1,2);
INSERT INTO myTable(Field1,Field1) VALUES(1,2);
INSERT INTO myTable(Field1,Field1) VALUES(1,2);
INSERT INTO myTable(Field1,Field1) VALUES(1,2);

Can this be done with one line, such as:
INSERT INTO myTable(Field1,Field1)
SELECT TOP 6 1,2
FROM SomeTableWithManyRows

I cant count on any table in my system having xxxxxx rows, not to mention
that would be sloppy programming.
[Maybe there is a MS table that I can count on having xxxxxx rows?]

Is there any smooth way to insert multiple records without using cursors?

Thanks,
Chris

Aaron Bertrand [SQL Server MVP]

2005-11-29, 7:05 pm

> I'm trying to insert xx identical records with 1 statement.

Why on earth would you want xx identical rows? I don't even want to count
how many principles this violates.

Why not add a count to your table?

INSERT INTO myTable(column1, column2, count) SELECT 1,2,6;

Jose G. de Jesus Jr MCP, MCDBA

2005-11-29, 7:05 pm

hi
hope this helps

declare @x int
select @x=0
while @x< 1000 --<<-- how many time you want this done
begin
select @x=@x+1
INSERT INTO myTable(Field1,Field1) VALUES(1,2)
end

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

"Chris" wrote:

> I'm trying to insert xx identical records with 1 statement.
>
> Basically, I want to avoid the following if I know I want to insert 6
> records:
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
>
> Can this be done with one line, such as:
> INSERT INTO myTable(Field1,Field1)
> SELECT TOP 6 1,2
> FROM SomeTableWithManyRows
>
> I cant count on any table in my system having xxxxxx rows, not to mention
> that would be sloppy programming.
> [Maybe there is a MS table that I can count on having xxxxxx rows?]
>
> Is there any smooth way to insert multiple records without using cursors?
>
> Thanks,
> Chris
>
>
>

Chris

2005-11-29, 7:05 pm

Because when the records are first inserted, they all have default values.
But each record must remain independant, because they can have unique
changes made and they all have unique primary keys.

Thinks of having 74 pairs of shoes, each is independant and has a primary
key [barcode].
Each can have changes made to the properties of the pair of shoes without
affecting the others.
Each pair of shoes belongs to a Batch of shoes. [one to many]

My simple example was to ask how to do something, not to get into theory.
My usage does not violate any principles I can think of, but I appreciate
your concern and suggestion.

-Chris

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:Op$mtpP9FHA.648@TK2MSFTNGP12.phx.gbl...
>
> Why on earth would you want xx identical rows? I don't even want to count
> how many principles this violates.
>
> Why not add a count to your table?
>
> INSERT INTO myTable(column1, column2, count) SELECT 1,2,6;
>

Aaron Bertrand [SQL Server MVP]

2005-11-29, 7:05 pm

All right, as you wish, have fun with loops and redundant data!

"Chris" <rooster575@hotmail.com> wrote in message
news:%23Qd6D6P9FHA.1248@TK2MSFTNGP14.phx.gbl...
> Because when the records are first inserted, they all have default values.
> But each record must remain independant, because they can have unique
> changes made and they all have unique primary keys.
>
> Thinks of having 74 pairs of shoes, each is independant and has a primary
> key [barcode].
> Each can have changes made to the properties of the pair of shoes without
> affecting the others.
> Each pair of shoes belongs to a Batch of shoes. [one to many]
>
> My simple example was to ask how to do something, not to get into theory.
> My usage does not violate any principles I can think of, but I appreciate
> your concern and suggestion.
>
> -Chris

Jose G. de Jesus Jr MCP, MCDBA

2005-11-29, 7:05 pm

insert into mytable(field1,field2)
select (x,y) from source where x=1 and y=2

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

"Chris" wrote:

> I'm trying to insert xx identical records with 1 statement.
>
> Basically, I want to avoid the following if I know I want to insert 6
> records:
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
>
> Can this be done with one line, such as:
> INSERT INTO myTable(Field1,Field1)
> SELECT TOP 6 1,2
> FROM SomeTableWithManyRows
>
> I cant count on any table in my system having xxxxxx rows, not to mention
> that would be sloppy programming.
> [Maybe there is a MS table that I can count on having xxxxxx rows?]
>
> Is there any smooth way to insert multiple records without using cursors?
>
> Thanks,
> Chris
>
>
>

Jose G. de Jesus Jr MCP, MCDBA

2005-11-29, 7:05 pm

insert into mytable(field1,field2)
select x,y from source where x=1 and y=2

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787

"Chris" wrote:

> I'm trying to insert xx identical records with 1 statement.
>
> Basically, I want to avoid the following if I know I want to insert 6
> records:
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
> INSERT INTO myTable(Field1,Field1) VALUES(1,2);
>
> Can this be done with one line, such as:
> INSERT INTO myTable(Field1,Field1)
> SELECT TOP 6 1,2
> FROM SomeTableWithManyRows
>
> I cant count on any table in my system having xxxxxx rows, not to mention
> that would be sloppy programming.
> [Maybe there is a MS table that I can count on having xxxxxx rows?]
>
> Is there any smooth way to insert multiple records without using cursors?
>
> Thanks,
> Chris
>
>
>

Sponsored Links