How to insert ten rows in one insert statement

Dear all,
I want to insert 10 rows data as follows
column-A column-B column-C
A A
1
A A
2
A A
3
A A
4
A A
5
In each row, the Column-A and column-B are the same. While column-c
will increase from 1 to 10.
How can I do that in one insert statement . Or I need to loop in VB
Program.
Thanks.If column C is an identity problem, then you just insert 'a', 'a' 10 times
(like your other question).
But otherwise, you could use rowcount and row_number like this:
set rowcount 10
insert into newtable
select 'a', 'a', row_number() over (order by quantity)
from sometable
where sometable has at least 10 rows.
Or, using a CTE instead of rowcount:
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable
)
insert into newtable select * from rownums where rownum <= 10
"hon123456" wrote:

> Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
> In each row, the Column-A and column-B are the same. While column-c
> will increase from 1 to 10.
> How can I do that in one insert statement . Or I need to loop in VB
> Program.
> Thanks.
>|||Thanks Rob.
When I try to run
select 'a', 'a', row_number() over (order by quantity) from sometable.
The query analyzer complains 'row_number' is not a recognized function
name.
By the way, can I have the complete sql statement for the follows
statement
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable )|||Thanks Rob. sorry to post again. But I am wondering what does the
variable "quantity" come from in the following statement:
select 'a', 'a', row_number() over (order by quantity) from sometable.|||Haha - sorry... that's just one of the columns in 'sometable'. I meant to
change that.
If it's not recognising row_number(), then perhaps you're not running
SQL2005 ? :(
It's somewhat harder to do in SQL2000. Off the top of my head, the easiest
way to do it is to create a temporary table with an identity field, or
perhaps use a while loop and do 10 single inserts (after all, if it's only
10...).
One nice way would be to use an auxiliary table of numbers. You'll find it
useful for all kinds of other reasons too... but most of all, you can easily
just say "select 'a', 'a', num from nums where num <= 10"
Hope this helps,
Rob
"hon123456" wrote:

> Thanks Rob. sorry to post again. But I am wondering what does the
> variable "quantity" come from in the following statement:
> select 'a', 'a', row_number() over (order by quantity) from sometable.
>|||If you already had a table of sequential numbers this would be
simpler. You would simply replace derived table A with that table,
and limit the values to the range 1 to 10 in a WHERE clause.
INSERT SomeTable
SELECT A.column_a, 'A' as column_b, 'A' as column_c
FROM (select 1 as column_a UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9 UNION
select 10) as A
Roy Harvey
Beacon Falls, CT
On 18 Apr 2006 21:48:17 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
>In each row, the Column-A and column-B are the same. While column-c
>will increase from 1 to 10.
>How can I do that in one insert statement . Or I need to loop in VB
>Program.
>Thanks.

How to insert empty string

Dear All,
I got a SQL statement like that
Insert into TableA (columnA,columnB,ColumnC) select
VariableA, VariableB,VariableC
Where columnA,columnB,ColumnC are Varchar. VariableA,
VariableB,VariableC are variables return by VB Function. If the
VariableA = A, VariableC = C, VariableB contains no chacracter then
the statemnet would become as follows:
Insert into TableA (columnA,columnB,ColumnC) select A,,C
The above Insert statement is not valid because two comma exist after
select A (,,). Therefore I need to make VariableB contains an empty
space, i.e VariableB = " " . But I do not want VariableB contains a
space. My question is how to make "Insert into TableA
(columnA,columnB,ColumnC) select A,,C"
valid without adding a space character to VariableB. That means how I
can insert VariableB without adding a space character to VariableB?
ThanksWhat about using single quotation --> '' ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can't get SQL server to accept a syntactically incorrect statement.
What you should be doing is a little interpretation on the VB side
before you fire off the T-SQL statement to the SQL server. My VB is a
little rusty but it would be something like:
dim a,b,c
dim SQL
..
SQL = "insert into tableA (colA, colB, colC) values ("
if (a = "") then SQL = SQL & "null, "
else SQL = SQL & a & ", "
if (b = "") then SQL = SQL & "null, "
else SQL = SQL & b & ", "
if (c = "") then SQL = SQL & "null)"
else SQL = SQL & c & ")"
..
send your T-SQL string off to the server now
..
It should be dead simple to write around particular cases when you're
preparing the SQL statement on the VB side. Alternately you could
specify "''" instead of "null" if you wanted an empty string in that
column rather than a null. There are a number of different ways you can
do it but they're all fiddling with the SQL string using VB before you
send it to the server.
*mike hodgson*
http://sqlnerd.blogspot.com
hon123456 wrote:

>Dear All,
> I got a SQL statement like that
>
> Insert into TableA (columnA,columnB,ColumnC) select
>VariableA, VariableB,VariableC
>Where columnA,columnB,ColumnC are Varchar. VariableA,
>VariableB,VariableC are variables return by VB Function. If the
>VariableA = A, VariableC = C, VariableB contains no chacracter then
>the statemnet would become as follows:
> Insert into TableA (columnA,columnB,ColumnC) select A,,C
>The above Insert statement is not valid because two comma exist after
>select A (,,). Therefore I need to make VariableB contains an empty
>space, i.e VariableB = " " . But I do not want VariableB contains a
>space. My question is how to make "Insert into TableA
>(columnA,columnB,ColumnC) select A,,C"
>valid without adding a space character to VariableB. That means how I
>can insert VariableB without adding a space character to VariableB?
>Thanks
>
>|||or omit that column
Insert into TableA (columnA,ColumnC) select A,C
Madhivanan|||Yes, but that would take slightly more effort on the VB side so I didn't
bother showing that one.
*mike hodgson*
http://sqlnerd.blogspot.com
Madhivanan wrote:

>or omit that column
>Insert into TableA (columnA,ColumnC) select A,C
>Madhivanan
>
>

How to insert data from another table

I want to Insert data from another table, I use the statement:
insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
St)
but it fail.
How can I do?insert into YearsClass (Years,Class)
Select distinct Years, ClassID from
St
http://sqlservercode.blogspot.com/
"ad" wrote:
> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?
>
>|||Do this
insert into YearsClass (Years,Class) Select distinct Years, ClassID
from
St
ad wrote:
> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?

How to INSERT a space?

Hi, how does one normally insert a space in a statement like this:

INSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +'blahblah')

In column2 the output looks like ' 2007-10-08 20:19:08blahblah', but I want it to be like '2007-10-08 20:19:08 blahblah' (two spaces between date and text).

Thanks,

Chris

why not this

CONVERT(varchar(19),Getdate(),120)+' '+'blahblah')

|||

INSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +' blahblah')orINSERT INTO table (column0, column1, column2)VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) +' ' +'blahblah')

|||

Hi

Use space function like this

space(0) or space(1) ....

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Also, you can use SqlServer function Space(SpaceNumber). In your case, you can do

VALUES (getdate(),'blah',CONVERT(VARCHAR(19),GETDATE(), 120) + Space(2) + 'blahblah')

|||

Yes, thanks. The Space(*) did it. Could've used the ' ' but that's kind of sloppy, IMO.

How to insert 4 records for every one found?

This SQL will insert one record into #Pivot1 for every one
record returned by the SELECT statement:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours,
wr.HoursAvailable
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID = hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
---
What I need to do, however, is to split each of the incoming records up
into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
for each record.
I guess the brute force method would be to just run that snippet of SQL
four times... but is there a "right" way?
--
PeteCresswellOops! I think I may have something:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours/4,
wr.HoursAvailable/4
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID =hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
---
Am I on the right track?
--
PeteCresswell|||RE/
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
"Oops" again: strike that last INSERT - it would
have given us 8 copies of each rec instead of 4...
--
PeteCresswell|||That wouldn't work, you'd end up with far too many records.
--
HTH
Ryan Waight, MCDBA, MCSE
"(Pete Cresswell)" <x@.y.z> wrote in message
news:0k3vqv0fr0s2763bmgfma27re6e4lbglhc@.4ax.com...
> Oops! I think I may have something:
> ---
> INSERT INTO #Pivot1
> (
> PersonID,
> ProjectID,
> Estimate5,
> Available5
> )
> SELECT
> wr.PersonID,
> ln.ProjectID,
> hr.Hours/4,
> wr.HoursAvailable/4
> FROM (tblWeekReported wr
> INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID =ln.WeekReportedID)
> INNER JOIN tblHour hr ON ln.WeekReportedLineID => hr.WeekReportedLineID
> WHERE
> (
> (wr.BeginDate=@.BeginDate5) AND
> (wr.EndDate=@.EndDate5) AND
> (wr.WeekType=2) -- 2=Monthly
> );
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> ---
> Am I on the right track?
> --
> PeteCresswell|||On Mon, 10 Nov 2003 13:05:03 GMT, "(Pete Cresswell)" <x@.y.z> wrote:
>This SQL will insert one record into #Pivot1 for every one
>record returned by the SELECT statement:
>...<snip>...
>What I need to do, however, is to split each of the incoming records up
>into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
>for each record.
>I guess the brute force method would be to just run that snippet of SQL
>four times... but is there a "right" way?
I don't know about right way, but here's a couple of ways:
a) after inserting these rows, select them and insert again (thus
doubling them). Then select them and insert them once more (thus
doubling the double, i.e. four rows for each original one)
b) create a temp table, populate with four records, and join to it in
the select part of your statement. A cartesian join (i.e. no criteria
for the join so that all rows from your temp table are selected and
joined with all rows from the rest of your query) will mean that each
row will appear four times in your result set. This would be my
preference, as it involves a single insert (except for the temp table :)
Of course, if you did this regularly enough, you might want to leave the
table of four rows around permanently, ready to join with. Kind of like
the DUAL table in Oracle these days, that provides a hack method for
returning results from functions without referencing a "real" table.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

How to improve this SQL statement?

I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
follow. Thanks.

------------------------
SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
WHERE
(D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
AND
(B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
AND
(A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
AND pLTV >= A.percentage))
AND C.MONTH = D.MONTH
AND C.LOAN_SIZE= B.loan_size
AND C.percentage = A.percentagetotal shot in the dark, please let me know if this works:SELECT DECISION_ID
, DECISION_NAME_ID
, MONTH
, LOAN_SIZE
, percentage
, ADMIN_FEE_HL_ID
, PNTY_ID
, OFFER_RATE_ID
FROM DECISION AS A
WHERE MONTH = (
SELECT max(MONTH)
FROM DECISION
)
AND LOAN_SIZE = (
SELECT max(loan_size)
FROM DECISION
WHERE MONTH = A.MONTH
)
AND percentage = (
SELECT max(percentage)
FROM DECISION
WHERE MONTH = A.MONTH
and loan_size = a.LOAN_SIZE
)
rudy
http://r937.com/|||Hi r937,

Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

Month, Loan_size, Percentage
8, 100, 70
4, 200, 50
6, 150, 80|||did you try it?

i tried it on your sample of 3 and it gave the row with

8, 100, 70

if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

i thought you wanted row integrity

for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

my query will always return a row, as long as there is at least one row in the table

perhaps you did not explain your problem correctly?

i tried to see what your query was attempting to do, but it's seriously messed up

or maybe i totally misunderstood you

rudy|||Hi Rudy,

Sorry it was my mistake. The query is working now since I missed
out something. Thank you very much.

How to impletement this query in SSIS package

Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?

update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1

Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps).|||

Hi Phil,

if I can't not write SQL script to impletement this, I have to use condition split (for column01<>-1 ) -> Sort->Merge Join(Join dbo.columnlist table )->Condition Split (column from dbo.columnlist is null), derived column (update column01 with -1) -> Union All.

it looks so complicated.

do you know is there any component that we can write script to update the input column set? I know we can use VBScript component to do this , but I'm wondering if we can use SQL Script.

|||I think you should perform a lookup transformation against "select column from dbo.columnlist".

Using the lookup error output, you can add a derived column that sets column01 to -1. Then, use a union all to join the normal lookup output with its error output.

Don't worry about the "and column01 <> -1" statement. It is worthless.|||

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

I tried with 'enable memeory restriction' and modify the SQL Statement , replacing the '=' with '<>', but it prompts error when I debug, saying 'no match rows', but actually I do have match rows.

|||

Jeff_LIU wrote:

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow).