Copy one row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copy one column to another

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

try:
update Mapping Set SID = SMappingID
where SMappingID IS NOT NULL|||

Quote:

Originally Posted by NamelessNumberheadMan

I have a table I need to make changes to. Dropping columns isn't my problem, but before I drop one of the columns I need to copy the values that aren't null to another existing column in the same table. So for psuedo-code it would be something like:

update Mapping Set SID = (select SMappingID from Mapping where SMappingID is not null)

I don't know if I should be doing this in a loop (which I'm not totally familiar with using) or if there is a better way to copy these values. If it helps my columns are -

ID (int, Not Null, PK)
SID (int, Not Null, FK)
PartID (int, Not Null, FK)
CompID (int, Not Null)
SMappingID (int, Null)

The query is simple:

UPDATE TABLENAME
SET NEWCOLUMN = SOURCECOLUMN WHERE SOURCECOLUMN IS NOT NULL.

Thanks.

Copy Image data type from one table to another

Hi,
I've a column col1 of image data type in table1. I would like to copy the data from col1 to another image column col2 in table2. Before moving the value, checking has to be done to specify which col1 data from table1 is needed and the destination has to be checked too.

Example: insert into col2
(select col1 from table1 where table1_id =5)
where table2_id =6

Hence bcp wouldn't work. Can anyone suggest me a way to do it. I tried using writetext but then, i've to get data from col1 in a variable, which is not possible. Any suggestions would be very helpful.

Thanks in advance.
Ramya.I found the way to do it. Here is the code:

declare @.ptrvalsource binary(16)

DECLARE @.ptrvaldest binary(16)

select @.ptrvalsource =textptr(col1) from table1 where table1_id = 5

SELECT @.ptrvaldest = TEXTPTR(col2) FROM table2 where table2_id=6

updateTEXT table2.col2 @.ptrvaldest 0 null table1.col1 @.ptrvalsource

Thanks,
Ramya.

Copy database wizard. Invalid column name 'AccountCode'

I've been trying the copy database wizard for hours, each time getting one error or another. The most recent one is "Invalid column name 'AccountCode'". In the error message it tells me to change the MaximumErrorCount - I can't find any information on how to do that.

I'm using VPN to access the source server. The source is SQL Server 2000 from the client's server. The destination is SQL Server 2005 from the MSDN Premium subscription. The username has every server role checked. Part of the transfer is successful. I'm using SMO instead of Attach & Detach.

What's wrong with 'AccountCode'?

How do I change MaximumErrorCount in the SQL Server Management studio?

TIA

Mike

what is service pack applied on this machine. is there any particular reason you are going for copy database wizards. have u tried Backup/restore or dettach/attach method. You apply sp2 for sqlserver 2005 and try CDW

Madhu

|||

I applied SQL Server SP2 as part of my attempts to get it working yesterday. The OS is XP and has had XP SP 2 for a while.

I tried the backup / restore yesterday and the database was created but I could not see any tables, views, sp, etc. I just now I tried it again but this time I checked the "Overwrite existing database" selection and it seems to have worked just fine this time.

So I'm good to go now - I still don't know why CDW didn't work. I had deleted the database each time I tried it yesterday so it couldn't be that.

Thanks for the reply. At least it got me to try the restore option again.

Mike

Copy database with encrypted column to new server and decrypt column there

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

It's actually a little easier than that. All you have to do is restore the database and then restore the encryption between the SMK and the DBMK. The database master key should already be in the database so after that you just need to associate the DBMK with the new server's service master key.

After restore, you will need to:

1) use <database_name>

2) open database master key

3) alter database master key add encryption by service master key

After that, everything should work as normal.

Sung

Copy data from one table to another table with change in identity column values

Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

If I understand your question, you want to insert the data from table
test into an already existing table.
>
INSERT existingtable
SELECT a, b
FROM test

I guess it should be:

INSERT existingtable
SELECT 10 + a, b
FROM test

since Salish wanted to change the values.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxOn Mon, 20 Aug 2007 21:54:50 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>since Salish wanted to change the values.

Thanks for catching that, Erland.

Roy

Copy column of data into another column in the same table

I have a column of digits I'd like to copy into another column in the
same table. How would I do this?

Thanks,

BillOn 18 Jul 2003 11:06:33 -0700 in comp.databases.ms-sqlserver,
billzimmerman@.gospellight.com (Bill) wrote:

>I have a column of digits I'd like to copy into another column in the
>same table. How would I do this?

update table set column1 = column2
--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)

Copy Column in SSIS

What is the use of the Copy Column in SSIS please help me

give me the example

regards

koti

Hi ya,

Copy column is only there to create new copies of the existing columns. Later you can do some transformation or do some aggregations for the new copies. You can create multiple copies of a column.

Hope this helps.

Cheers

Rizwan

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

copy a record?

Hi,
I need to copy a record in a table that has an identity column. Plus,
I want to copy this record without using a column list in the SQL
statement.
So I searched the net, came up with an elegant solution:
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
however, when tried that I received:
Error 8101 An explicit value for the identity column in table can only
be specified when a column list is used and IDENTITY_INSERT is ON
So searched again and had the following answers:
"...Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
" and someone added:
"...
You need a column list for your INSERT statement:
INSERT t2 (
[id], [first], [org], [rest], [aux] ) SELECT
[ie], [first], [org], [rest], [aux] FROM t1
..."
BUT - I have to ask:
what if I want to insert without having to use a column list? if
i use a column list, then every time a column is added, modified or
deleted I need to change this store procedure too, which results in
doubling maintenance costs.
any ideas? how can I copy a record without using a named column
list?
Thanks very much for ANY idea,
Lior<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
Generate the SQL (including the column list) yourself at runtime
Liz
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?|||FWIW, anyone who submits SQL, or SPL that has INSERT/UPDATE statements that
do not have column lists, gets their code back, I won't even allow it on QA.
The reason is maintenance. If I add a column to a table, the code will
break, and then I have to dig it out of the system to do a hot-fix.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||WHY do you not want to do a column list' I can think if several reasons
why you SHOULD do one.
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
>|||Thanks to all answers. the reason I DONT want a coloumn list is to
avoid having the code breaks when I add/modify/delete a column. I want
to minimize the explicit names of columns and to have something like
INSERT INTO <table>
SELECT *
FROM table WHERE (table.ID = @.objID)
See? Here I have only one column name. therefore, less maintainence.
does it make sense? thanks for any ideas
thanks,
Lior
Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> WHY do you not want to do a column list' I can think if several reasons
> why you SHOULD do one.
w
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <liormessin...@.gmail.com> wrote in message
> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > Hi,
> > I need tocopy a recordin a table that has an identity column. Plus,
> > I want to copy this record without using a column list in the SQL
> > statement.
> > So I searched the net, came up with an elegant solution:
> > INSERT INTO <table>
> > SELECT *
> > FROM table WHERE (table.ID = @.objID)
> > however, when tried that I received:
> > Error 8101 An explicit value for the identity column in table can only
> > be specified when a column list is used and IDENTITY_INSERT is ON
> > So searched again and had the following answers:
> > "...Before your SQL Statement:
> > SET IDENTITY_INSERT <tablename> ON
> > " and someone added:
> > "...
> > You need a column list for your INSERT statement:
> > INSERT t2 (
> > [id], [first], [org], [rest], [aux] ) SELECT
> > [ie], [first], [org], [rest], [aux] FROM t1
> > ..."
> > BUT - I have to ask:
> > what if I want to insert without having to use a column list? if
> > i use a column list, then every time a column is added, modified or
> > deleted I need to change this store procedure too, which results in
> > doubling maintenance costs.
> > any ideas? how can Icopy a recordwithout using a named column
> > list?
> > Thanks very much for ANY idea,
> > Lior|||Actually, very interesting - here is a great thread I found about the
subject. My problem is exactly as Chris's
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/e59a1ef42bafeff9/23480e2a7fc6dc7a?lnk=gst&q=copy+a+record&rnum=2#23480e2a7fc6dc7a|||The real root problem here seems to be your developement process!! If you
aren't doing an impact analysis when you alter a table and making necessary
modifications to your code then that needs to be rectified, IMHO. BTW, you
can STILL make things work by simply using NULLable columns and/or defining
defaults for your tables, right?
--
TheSQLGuru
President
Indicium Resources, Inc.
<liormessinger@.gmail.com> wrote in message
news:1189040443.208198.139120@.o80g2000hse.googlegroups.com...
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
>
>
>
>
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> WHY do you not want to do a column list' I can think if several reasons
>> why you SHOULD do one.
> w
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <liormessin...@.gmail.com> wrote in message
>> news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
>> > Hi,
>> > I need tocopy a recordin a table that has an identity column. Plus,
>> > I want to copy this record without using a column list in the SQL
>> > statement.
>> > So I searched the net, came up with an elegant solution:
>> > INSERT INTO <table>
>> > SELECT *
>> > FROM table WHERE (table.ID = @.objID)
>> > however, when tried that I received:
>> > Error 8101 An explicit value for the identity column in table can only
>> > be specified when a column list is used and IDENTITY_INSERT is ON
>> > So searched again and had the following answers:
>> > "...Before your SQL Statement:
>> > SET IDENTITY_INSERT <tablename> ON
>> > " and someone added:
>> > "...
>> > You need a column list for your INSERT statement:
>> > INSERT t2 (
>> > [id], [first], [org], [rest], [aux] ) SELECT
>> > [ie], [first], [org], [rest], [aux] FROM t1
>> > ..."
>> > BUT - I have to ask:
>> > what if I want to insert without having to use a column list? if
>> > i use a column list, then every time a column is added, modified or
>> > deleted I need to change this store procedure too, which results in
>> > doubling maintenance costs.
>> > any ideas? how can Icopy a recordwithout using a named column
>> > list?
>> > Thanks very much for ANY idea,
>> > Lior
>|||On 4 Sep, 23:19, liormessin...@.gmail.com wrote:
> Hi,
> I need to copy a record in a table that has an identity column. Plus,
> I want to copy this record without using a column list in the SQL
> statement.
> So I searched the net, came up with an elegant solution:
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> however, when tried that I received:
> Error 8101 An explicit value for the identity column in table can only
> be specified when a column list is used and IDENTITY_INSERT is ON
> So searched again and had the following answers:
> "...Before your SQL Statement:
> SET IDENTITY_INSERT <tablename> ON
> " and someone added:
> "...
> You need a column list for your INSERT statement:
> INSERT t2 (
> [id], [first], [org], [rest], [aux] ) SELECT
> [ie], [first], [org], [rest], [aux] FROM t1
> ..."
> BUT - I have to ask:
> what if I want to insert without having to use a column list? if
> i use a column list, then every time a column is added, modified or
> deleted I need to change this store procedure too, which results in
> doubling maintenance costs.
> any ideas? how can I copy a record without using a named column
> list?
> Thanks very much for ANY idea,
> Lior
Please do NOT multi-post. You have several replies in the
microsoft.public.sqlserver.programming newsgroup.
Posting independently to multiple groups wastes everyones time and
makes it harder for people to help you.
--
David Portas|||On Sep 6, 7:00 am, liormessin...@.gmail.com wrote:
> Thanks to all answers. the reason I DONT want a coloumn list is to
> avoid having the code breaks when I add/modify/delete a column. I want
> to minimize the explicit names of columns and to have something like
> INSERT INTO <table>
> SELECT *
> FROM table WHERE (table.ID = @.objID)
> See? Here I have only one column name. therefore, less maintainence.
> does it make sense? thanks for any ideas
> thanks,
> Lior
> Sep 5, 9:05 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > WHY do you not want to do a column list' I can think if several reasons
> > why you SHOULD do one.
> w
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <liormessin...@.gmail.com> wrote in message
> >news:1188944370.602740.106640@.k79g2000hse.googlegroups.com...
> > > Hi,
> > > I need tocopy a recordin a table that has an identity column. Plus,
> > > I want to copy this record without using a column list in the SQL
> > > statement.
> > > So I searched the net, came up with an elegant solution:
> > > INSERT INTO <table>
> > > SELECT *
> > > FROM table WHERE (table.ID = @.objID)
> > > however, when tried that I received:
> > > Error 8101 An explicit value for the identity column in table can only
> > > be specified when a column list is used and IDENTITY_INSERT is ON
> > > So searched again and had the following answers:
> > > "...Before your SQL Statement:
> > > SET IDENTITY_INSERT <tablename> ON
> > > " and someone added:
> > > "...
> > > You need a column list for your INSERT statement:
> > > INSERT t2 (
> > > [id], [first], [org], [rest], [aux] ) SELECT
> > > [ie], [first], [org], [rest], [aux] FROM t1
> > > ..."
> > > BUT - I have to ask:
> > > what if I want to insert without having to use a column list? if
> > > i use a column list, then every time a column is added, modified or
> > > deleted I need to change this store procedure too, which results in
> > > doubling maintenance costs.
> > > any ideas? how can Icopy a recordwithout using a named column
> > > list?
> > > Thanks very much for ANY idea,
> > > Lior- Hide quoted text -
> - Show quoted text -
When you do not explicitly mention the column it can cause very hard
to find bugs where wrong columns get populated! Tust me on this
because it happened to me. Also, if you add/delete/modify a column,
your code will break when you do not have a column list.

Copy a column content in another one only if empty

for MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you--I ve consider null value also in this code.
update Users
set Alias=Name where Alias is null or ltrim(rtrim(Alias))=''|||wonderfull !

it works :-)

thanks a lot

Coplicated view - picking from a=b or from c&d =e

I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
JohnRocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
> > I realized there was something wrong with my view when an entire line
> > was missing. I believe it is caused by an inner join I did on one
> > column to create another column which may not actually have a value.
> > let me show you.
> >
> > Table A ViewA ViewB
> > type1 type1
> > type2 type2
> > type3 type3
> > answer answer
> >
> > Pretend that any / all items in Table A can be null. What I want the
> > view to do is either 1) have both viewA and ViewB answer show up even
> > if its null or 2) try to get viewB answer but if not, then go to ViewA
> > answer, then null.
> >
> > Yes, that is a VIEW doing this...or so I hope.
> >
> > Any ideas?
> >
> > John
> >|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help

Cool Query Analyzer Trick

In Query Analyzer you can save a lot of time by using this trick instead of
typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window
I work with people who are certified, have 10 years experience and none of
them knew this trick
Also if you know of any other not well known QA tricks let me know and I
will update my blog
http://sqlservercode.blogspot.com/
Here are some other nice shortcuts (at least in SQL2k and below)
Ctrl-R
Ctrl-E
F6
Highlight some text and hit Ctrl-U or Ctrl-L
Highlight some text and hit Shift+Ctrl+C
now try Shift+Ctrl+R
Keith
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||On Thu, 22 Sep 2005 13:28:03 -0500, Keith Kratochvil wrote:

>Here are some other nice shortcuts (at least in SQL2k and below)
>Ctrl-R
>Ctrl-E
>F6
>Highlight some text and hit Ctrl-U or Ctrl-L
Hi Keith,
I assume that you meeant Ctrl-Shift-U and Ctrl-Shift-L?
(Ctrl-U is change database and Ctrl-L is display estimated execution
plan).

>Highlight some text and hit Shift+Ctrl+C
>now try Shift+Ctrl+R
And to get to know all keyboard shortcuts, study the menu's. All
shortcuts are listed in the menu's.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You can also right click on the table and generate a Select, insert , Update
or Delete statement as well.
Andrew J. Kelly SQL MVP
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:CF26A028-8083-49E0-8CEE-8858A65D8C3F@.microsoft.com...
> In Query Analyzer you can save a lot of time by using this trick instead
> of
> typing all the column names of a table
> Hit F8, this will open Object Browser
> Navigate to DatabaseName/TableName/Columns
> Click on the column folder and drag the column folder into the Code Window
> Upon release you will see that all the column names are in the Code Window
> I work with people who are certified, have 10 years experience and none of
> them knew this trick
> Also if you know of any other not well known QA tricks let me know and I
> will update my blog
> http://sqlservercode.blogspot.com/
>
|||As a follow on to Andrews tip, after generating an insert, delete or
update statement, press Ctrl-Shift-M...
Jacko

Converting/Casting strings into Datetime datatype

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?

Thanks!

If you had only stored your date values in the ISO format of YYYYMMDD, they would easily cast or convert to datetime. -Or even left in one of the standard date delimiters, such as [ / - ].

However, you (or some unnamed 'other' person) made up a oddball format, and now you will have to 'handle' it to create a 'real' date value everytime you need to use it.

(This assumes your format is MMDDYYYY.)

SELECT cast( stuff( stuff( '03032007', 3, 0, '/' ), 6, 0, '/' ) AS datetime )

-
2007-03-03 00:00:00.000

|||

You know that MS stores sqlagent datetime in to two int columns with the following format, right? (Take a look at the schema for msdb: sysalerts,sysjobhistory, sysjobschedules, sysjobservers, and sysjobsteps.)

date: YYYYMMDD

time: HHMMSS

So, it's not that weird to see the public employs such schema.

|||

But I also notice that MS stores SQL Agent datetime in ISO format (YYYYMMDD).

That little 'standard' makes a lot of difference in cast/convert.

That is behind my even mentioning using a standard ISO format in my response...

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

Converting varchar to int

Hi,
I have a varchar(255) column where I may have data like this:
43294430949
adkk3400
1056
ff1d
10
302
15000043
I would like to write a SQL query that returns values between
10 and 500 (numeric)
If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect
I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500
but it fails when I have characters in the column.
Do you guys know how I can do that?
ThanksFirst you need a solid function that can determine if the value is numeric.
For
that go here: http://www.aspfaq.com/show.asp?id=2390.
Select *
From #Test As T
Where IsNumeric(T.Data) = 1
And dbo.IsReallyInteger(T.Data) = 1
And Cast(T.Data As BigInt) Between 10 And 50
Why the two checks? If you only use IsReallyNumeric, SQL cannot determine wh
at
that function actually does and more specifically, whether it filters for va
lues
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int, colum
n)
> <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks|||Try,
Select *
from table
where
case
when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
else null
end between 10 and 500
AMB
"Star" wrote:

> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks
>|||You can write your query with a WHERE clause like:
WHERE CASE WHEN ISNUMERIC( col ) = 1
THEN CAST( col AS BIGINT )
END BETWEEN 10 AND 500 ;
Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.
Anith|||We need more information:
1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?
2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?
So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):
SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500
If this is not what you are looking for, you will need to provide better
specifications.
P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri :)
Uri Dimant wrote:
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> > Hi
> > Not being a coder I have no idea how to do this and existing posts dont
> > really help.
> >
> > I have a table called 'Incoming' and a column called dateofbirth. The
> > format of the column is varchar. The values in the dateof birth column
> > are 01012000. When searching for particular dates I'm getting crap
> > results (because the query is crap too). I reckon I need to convert the
> > values in the column to dates so that my query can work.
> > All help gratefully accepted.
> > TIA
> >|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),11
2)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri
Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),
112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting time to day

Hi All,
I am pretty new to sql server. I have a table with a column which has
data in form of time,e.g.:
2006-10-15 08:06:29.000
I want to select data from the table using only date part of the time
(I want to group the data by day)
Can you please help me?
Thanks.You can use the Floor() function.
http://msdn2.microsoft.com/en-us/library/ms178531.aspx
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<jack.smith.sam@.gmail.com> wrote:
| Hi All,
|
| I am pretty new to sql server. I have a table with a column which has
| data in form of time,e.g.:
| 2006-10-15 08:06:29.000
|
| I want to select data from the table using only date part of the time
| (I want to group the data by day)
| Can you please help me?
|
| Thanks.
||||You can use CONVERT to change datetime formats. To return or
group by just the date, something along the lines of:
convert(char(10), YourDateColumn, 101)
-Sue
On 15 Oct 2006 17:55:47 -0700, jack.smith.sam@.gmail.com
wrote:

>Hi All,
>I am pretty new to sql server. I have a table with a column which has
>data in form of time,e.g.:
>2006-10-15 08:06:29.000
>I want to select data from the table using only date part of the time
>(I want to group the data by day)
>Can you please help me?
>Thanks.|||One method is with DATEADD/DATEDIFF:
SELECT DATEADD(day,0,DATEDIFF(day,0,('20061015 08:06:29.000')))
Hope this helps.
Dan Guzman
SQL Server MVP
<jack.smith.sam@.gmail.com> wrote in message
news:1160960147.761712.173580@.i42g2000cwa.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a table with a column which has
> data in form of time,e.g.:
> 2006-10-15 08:06:29.000
> I want to select data from the table using only date part of the time
> (I want to group the data by day)
> Can you please help me?
> Thanks.
>