Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +
> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:
>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Friday, March 30, 2012
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.
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.
Wednesday, March 28, 2012
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
>
>
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
>
>
Monday, March 19, 2012
how to import MySql dump file?
Dear sir,
I got a file in the format of MySlq dump .sql file. It is about 120mb.
However should I import it into my MSDE database? I have MSDE in my XP pc,
but I do have a Enterprise manager on my PC as well.
Thanks.Hi
MySQL is not a Microsoft product. You need to load your MySQL dump into a
MySQL database installation. From there, you need to BCP or export the data
to MSDE.
A direct load is not possible.
Regards
Mike
"Guoqi Zheng" wrote:
> Dear sir,
> I got a file in the format of MySlq dump .sql file. It is about 120mb.
> However should I import it into my MSDE database? I have MSDE in my XP pc
,
> but I do have a Enterprise manager on my PC as well.
> Thanks.
>
>
I got a file in the format of MySlq dump .sql file. It is about 120mb.
However should I import it into my MSDE database? I have MSDE in my XP pc,
but I do have a Enterprise manager on my PC as well.
Thanks.Hi
MySQL is not a Microsoft product. You need to load your MySQL dump into a
MySQL database installation. From there, you need to BCP or export the data
to MSDE.
A direct load is not possible.
Regards
Mike
"Guoqi Zheng" wrote:
> Dear sir,
> I got a file in the format of MySlq dump .sql file. It is about 120mb.
> However should I import it into my MSDE database? I have MSDE in my XP pc
,
> but I do have a Enterprise manager on my PC as well.
> Thanks.
>
>
Friday, March 9, 2012
How to import & export stored procedure in sql query like Table data ?
Dear all,
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapatiHi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:
> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapatiHi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:
> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati
How to import & export stored procedure in sql query like Table data ?
Dear all,
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapati
Hi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:
> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati
How to import & export stored procedure in sql query like Table data ?
thanks,
harshad prajapati
Hi
You can script the stored procedures using SMO or in SSMS. You should use
version control keep the code in it, then you know that you are scripting a
particular release/revision.
You can also use SSIS to transfer objects (not only stored procedures).
John
"harshad" wrote:
> Dear all,
> How to import & export stored procedure in sql query like Table data ?
> thanks,
> harshad prajapati
Subscribe to:
Posts (Atom)