how to insert text / bitmap column
example pls :-)Originally posted by ligang
how to insert text / bitmap column
example pls :-)
Try the WRITETEXT and UPDATETEXT statements, examples can be found in BOL.
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 30, 2012
How to insert right justifies leading zeros
I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this
Chintan.
Here it is:SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)
Thanks,
Zuomin
|||
The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?
If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.
You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.
|||the rule applies to about 9600 rows so....what can be the best solution
Chintan
|||as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.
|||OK, but what is the datatype of your column?
|||Varchar.
|||I would suggest using something like zuomin's RIGHT solution.
|||A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.
|||the problem here is 1. there is no primary keys
2. there is no application which will insert leading zeros
3. it is goin as a simple text files
so now if i use
SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)
this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012
it might some kinda update statment
Chintan
|||UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)
Without WHERE condtion, you will update all your records.How to insert picture into Image column?
I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql
How to Insert or Update Records from Source to Destination using Oracle 8i DB
Hi !
I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.
For Exmaple :
Source Table Name : tbl_source
following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip
table contain only one records:GRD1,SRD1,FRD1,100,Product
I want Insert the Destiantion table the Follwing Condition. using Conditional Split.
1)Cond1 (!(ISNULL(GRD1))
2)Cond2 !(ISNULL(SRD1))
3)Cond3 !(ISNULL(FRD1))
I need the Following output
Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)
Coulmn Name , Column Value , ID
Row 1 GRD GRD1 100
Row 2 SRD SRD1 100
Row 3 FRD FRD1 100
How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.
Thanks & regards
M.Jeyakumar
Hi,
You can not do it thru Conditional split. Use script component to do this.
|||Have you try using the Multicast task?|||Hi , thanks for your repley. can you give samples control flow for this one .
Thanks & regards
Jeyakumar
|||Maybe something like this...
Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1
Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2
Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3
One Multicast Task that split into 3 transformations.
|||Hi HienPhilly,
Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.
There are two Tables .
1) Product_ tbl
2) Master_Prod_tbl
Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl
How to achieve this one ?
the following data flow i am using
1) Product_tbl -> Lookup-->Dstination table
in the Lookup the follwing sql query i wrote:
Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m
WHERE p.CODE=m.CODE
but it didn't work? pls give me your suggestion.
Thanks & Regards
Jeyakumar.M
|||I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...
|||i think you mean "transformation" instead of "task".|||Hienphilly wrote:
Maybe something like this...
Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1
Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2
Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3
One Multicast Task that split into 3 transformations.
Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.
I need another Help using Oracle 8i Database
i have two sql statement :
1) Source Sql Statement(select)
2) Update or Insert
How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.
Thanks & Regards,
M.Jeyakumar
|||i have two sql statement :
1) Source Sql Statement(select)
2) Update or Insert
How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.
Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.
-Jamie
|||Hi ,
Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.
Using BI How to INSERT Or UPdate Records from Source to Destination.
Condition.:
1) The source records are New Records t hen The Destination should b e INSERTED
2) Already is there then it should be UPDATED
.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.
can i use the Sql command for Insert or Update with Where Condition.
Thanks & regards,
M.Jeyakumar
|||Hi ,
Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.
Using BI How to INSERT Or UPdate Records from Source to Destination.
Condition.:
1) The source records are New Records t hen The Destination should b e INSERTED
2) Already is there then it should be UPDATED
.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.
can i use the Sql command for Insert or Update with Where Condition.
Thanks & regards,
M.Jeyakumar
I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.
-Jamie
How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge
Hi !
I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.
For Exmaple :
Source Table Name : tbl_source
following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip
table contain only one records:GRD1,SRD1,FRD1,100,Product
I want Insert the Destiantion table the Follwing Condition. using Conditional Split.
1)Cond1 (!(ISNULL(GRD1))
2)Cond2 !(ISNULL(SRD1))
3)Cond3 !(ISNULL(FRD1))
I need the Following output
Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)
Coulmn Name , Column Value , ID
Row 1 GRD GRD1 100
Row 2 SRD SRD1 100
Row 3 FRD FRD1 100
How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.
Thanks & regards
M.Jeyakumar
Hi,
You can not do it thru Conditional split. Use script component to do this.
|||Have you try using the Multicast task?|||Hi , thanks for your repley. can you give samples control flow for this one .
Thanks & regards
Jeyakumar
|||Maybe something like this...
Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1
Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2
Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3
One Multicast Task that split into 3 transformations.
|||Hi HienPhilly,
Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.
There are two Tables .
1) Product_ tbl
2) Master_Prod_tbl
Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl
How to achieve this one ?
the following data flow i am using
1) Product_tbl -> Lookup-->Dstination table
in the Lookup the follwing sql query i wrote:
Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m
WHERE p.CODE=m.CODE
but it didn't work? pls give me your suggestion.
Thanks & Regards
Jeyakumar.M
|||I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...
|||i think you mean "transformation" instead of "task".|||Maybe something like this...
Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1
Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2
Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3
One Multicast Task that split into 3 transformations.
Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.
I need another Help using Oracle 8i Database
i have two sql statement :
1) Source Sql Statement(select)
2) Update or Insert
How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.
Thanks & Regards,
M.Jeyakumar
|||i have two sql statement :
1) Source Sql Statement(select)
2) Update or Insert
How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.
Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.
-Jamie
|||Hi ,
Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.
Using BI How to INSERT Or UPdate Records from Source to Destination.
Condition.:
1) The source records are New Records t hen The Destination should b e INSERTED
2) Already is there then it should be UPDATED
.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.
can i use the Sql command for Insert or Update with Where Condition.
Thanks & regards,
M.Jeyakumar
|||Hi ,
Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.
Using BI How to INSERT Or UPdate Records from Source to Destination.
Condition.:
1) The source records are New Records t hen The Destination should b e INSERTED
2) Already is there then it should be UPDATED
.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.
can i use the Sql command for Insert or Update with Where Condition.
Thanks & regards,
M.Jeyakumar
I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.
-Jamie
How to insert multiple values to a column at a time?
Hi all,
I was looking to insert multiple values to a single column at a time in SQl.
Any help pleas!
Thank you
Ephi:
What exactly do you mean by "multiple values"? Different data types? A vector? An array? What exactly.
Dave
Lets say I have a single Column called X and I want to insert multiple values into X like (1,2,3) at a time through using the insert statement.
Thank you in advance.
|||Hi,In reality it is not acceptable according to data normalization rules.
I.e. if you want to have such behavior you should better create an additional table and have foreign constraints mapping to it.
Some example:
table_x(
field_1 .....,
field_2 .....,
field_in_which_you_want_to_have_multiple_values.....
);
table_y(
value_identifier .....,
value nvarchar(1024) ....
);
So you firstly insert several values to table 'table_y' and then just add
value_identifier to a 'table_x.field_in_which_you_want_to_have_multiple_values'|||
do you mean you want to create multiple rows, with one row for each value, using a single Insert statement?
This is not possible. Insert only creates one row in the table.
Unless, of course, you are inserting into one table using the values from another table, in which case you can use the insert...select syntax.
Normal insert syntax:
insert table_name
(col1, col2, col3)
values
(val1, val2, val3)
only inserts one row.
|||I would recommend you use Itzik Ben-Gan's Split function:CREATE FUNCTION dbo.fn_SplitTSQL
(@.arr NVARCHAR(MAX), @.separator NVARCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@.arr, n), @.separator, '')) + 1 AS pos,
SUBSTRING(@.arr, n,
CHARINDEX(@.separator, @.arr + @.separator, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@.arr)
AND SUBSTRING(@.separator + @.arr, n, 1) = @.separator;Once you've got this, you could do something like:
insert into mytable (col1, col2, col3)
select 'Val1', 'Val2', element
from dbo.fn_SplitTSQL(N'1,2,3',N',')
This should handle it nicely for you. Oh yes, and you'll need a table called Nums with a field called 'n', which you have populated from 1 to some arbitrarily large number. 1000 might be big enough for most of your uses...
There's more on this at:
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt
Robsql
How to insert multiple values in a single column
I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries.
In Col1, Col2 and Col3 there can be any number of values.
Company
why don't you want to use a sub-report?
It takes very long time, because i have 17 columns in the report.. its not fisible to insert 15 sub-reports
||| Ah I understand, and agree. Not sure how else to implement this though...could you insert another table into your main table's cells?
Not resolved yet... any ideas...
|||One idea would be to build your data using a view. Capture the main data (Company, Bank, ID) and then capture each columns' data (based on the ID in first query). Select from the view.
I think your data would be more like:
ID Company Bank ManagerNames BranchOffices TopSalesNames
1 CompanyA BankOne Jerry, Ted, Lisa Omaha, Chicago Fred, Mary
2 CompanyB BankTwo Paul Lincoln, Springfield, Florence William, John
Not sure if this meets your needs. You may be able to format the multi-value rows once in the report. Hope this helps.
|||thanks... I will try this
How to insert into Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table
Maybe something like?
|||insert into #TempResult
select distinct
Memberid,
Month,
Year
from Rebate a
where not exists
( select 0 from #TempResult b
where a.memberid = b.memberid
and a.month = b.month
and a.year = b.year
)Dave
If you are using SQL Server 2005, you can also use the EXCEPT operator:
insert into #TempResult
select Memberid,
Month,
Year
from Rebate
except
select Memberid,
Month,
Year
from #TempResult
Lax:
You do not; I choose zero because the column selected in this case doesn't matter; what matters is whether or not the row exists. This is a semi-join? Can somebody confirm the semi-join?
|||( Maybe a left anti semi join )Dave
Wednesday, March 28, 2012
How to INSERT DBNull.Values ? ...
Hello,
If I have a database table with column that can accept null values and I want to insert a null value into that column, what is the correct syntax?
db_cmd = New SqlCommand( "sp_stored_procedure", db_connection )
db_cmd.CommandType = CommandType.StoredProcedure
db_cmd.Parameters.Add( "@.col_to_set_to_null", ? )
ie: what expression can be used for "?"
Ideally, I want to check if a string value is empty and if so insert a null, but I have yet to see any examples, ie:
if ( str.empty ) then
insert DBNull.value into column
else
insert str into column
end if
Sincerely,
Brent D.db_cmd.Parameters.Add( "@.col_to_set_to_null", System.DBNull.Value )
How to insert data into xml column
I want to insert :
works perfectly, thanks.
<column>
<column_name>feedbackloop</column_name>
<column_type>INT</column_type>
</column>
at the end of the existing xml.
Perhaps it will be easier if i proved the schema and existing xml:
Code Snippet
create xml schema collection email_column_xml_schema as
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="grs" targetNamespace="grs" elementFormDefault="qualified">
<xsd:element name="object">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="type" type="xsd:string"/>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="remote" type="xsd:integer"/>
<xsd:element name="provider" type="xsd:string" />
<xsd:element name="connectionstring" type="xsd:string" />
<xsd:element name="database_name" type="xsd:string" />
<xsd:element name="send_using" type="xsd:string"/>
<xsd:element name="ftp_server" type="xsd:string" />
<xsd:element name="ftp_user" type="xsd:string" />
<xsd:element name="ftp_password" type="xsd:string" />
<xsd:element name="ftp_port_number" type="xsd:decimal" />
<xsd:element name="message" type="xsd:string" />
<xsd:element name="subject" type="xsd:string" />
<xsd:element name="recipients" type="xsd:string" />
<xsd:element name="as_attachment" type="xsd:integer" />
<xsd:element name="file_name" type="xsd:string" />
<xsd:element name="delimeter" type="xsd:string" />
<xsd:element name="send_interval_days" type="xsd:integer"/>
<xsd:element name="AlwaysSend" type="xsd:integer"/>
<xsd:element name="enabled" type="xsd:integer"/>
<xsd:element name="column" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="column_name" type="xsd:string" />
<xsd:element name="column_type" type="xsd:string" />
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go
CREATE TABLE email_column (email_id INT PRIMARY KEY IDENTITY(1,1), xColumn XML(email_column_xml_schema))
go
insert email_column values( '<object xmlns="grs">
<type>VIEW</type>
<name>snapshot</name>
<remote>1</remote>
<provider>SQLNCLI</provider>
<connectionstring></connectionstring>
<database_name></database_name>
<send_using>email</send_using>
<ftp_server></ftp_server>
<ftp_user></ftp_user>
<ftp_password></ftp_password>
<ftp_port_number>0</ftp_port_number>
<message>Snapshot</message>
<subject>Snapshot</subject>
<recipients></recipients>
<as_attachment>0</as_attachment>
<file_name></file_name>
<delimeter></delimeter>
<send_interval_days>1</send_interval_days>
<AlwaysSend>0</AlwaysSend>
<enabled>1</enabled>
<column>
<column_name>day</column_name>
<column_type>DATETIME</column_type>
</column>
<column>
<column_name>domainname</column_name>
<column_type>VARCHAR(50)</column_type>
</column>
<column>
<column_name>ratio</column_name>
<column_type>DECIMAL(10,2)</column_type>
</column>
</object>' )
go
Your schema has a target namespace 'grs' while the XML you are trying to insert has the namespace 'grscorp'. So that sample above does not fit the schema. If I change the XML to have namespace 'grs' then it is possible to insert the XML data. And then the following UPDATE statement works for me to insert the column element:
Code Snippet
UPDATE email_column
SET xColumn.modify('
declare default element namespace "grs";
insert
<column>
<column_name>feedbackloop</column_name>
<column_type>INT</column_type>
</column>
into (/object)[1]
');
||| works perfectly, thanks.how to insert characters in existing field
Hi,
I have a sql table with a field name model. In the model column, i have
model number that start with FX%.
I want to add WM before FX%. After FX can be anything, characters or
numbers. The model data type is varchar.
How can i do it. I tried using the syntax below but not successful.
update test2 set model = 'WM%' where model = 'FX%'
Can anyone help?
Tiffany,
It sounds like what you want is
update test2 set
model = 'WM' + model
where model = 'FX%'
This will paste WM on the beginning of all the FX... model names.
Steve Kass
Drew University
Tiffany wrote:
>Hi,
>I have a sql table with a field name model. In the model column, i have
>model number that start with FX%.
>I want to add WM before FX%. After FX can be anything, characters or
>numbers. The model data type is varchar.
>How can i do it. I tried using the syntax below but not successful.
>update test2 set model = 'WM%' where model = 'FX%'
>Can anyone help?
>
|||Steve!
I have a similar problem where I want to replace the domain part in a column
with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
tip you gave Tiffany but nothing happens.
Any clues?
Regards,
Bosse
"Steve Kass" wrote:
> Tiffany,
> It sounds like what you want is
> update test2 set
> model = 'WM' + model
> where model = 'FX%'
> This will paste WM on the beginning of all the FX... model names.
> Steve Kass
> Drew University
> Tiffany wrote:
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8241
On Wed, 16 Feb 2005 07:09:06 -0800, Bosse wrote:
>Steve!
>I have a similar problem where I want to replace the domain part in a column
>with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
>tip you gave Tiffany but nothing happens.
>Any clues?
>Regards,
>Bosse
Hi Bosse,
Steve's message to Tiffany was about how to put some extra characters in
front of existing string data. To replace a part of existing string data,
you use the REPLACE function instead.
UPDATE MyTable
SET Email = REPLACE (Email, '@.xxx.se', '@.yyy.se')
WHERE Email LIKE '%@.xxx.se'
Note the extra @. and .se inserted in the replace, to make sure that an
address like aaxxxt@.xxx.se is not accidentaly changed to aayyyt@.yyy.se.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
I have a sql table with a field name model. In the model column, i have
model number that start with FX%.
I want to add WM before FX%. After FX can be anything, characters or
numbers. The model data type is varchar.
How can i do it. I tried using the syntax below but not successful.
update test2 set model = 'WM%' where model = 'FX%'
Can anyone help?
Tiffany,
It sounds like what you want is
update test2 set
model = 'WM' + model
where model = 'FX%'
This will paste WM on the beginning of all the FX... model names.
Steve Kass
Drew University
Tiffany wrote:
>Hi,
>I have a sql table with a field name model. In the model column, i have
>model number that start with FX%.
>I want to add WM before FX%. After FX can be anything, characters or
>numbers. The model data type is varchar.
>How can i do it. I tried using the syntax below but not successful.
>update test2 set model = 'WM%' where model = 'FX%'
>Can anyone help?
>
|||Steve!
I have a similar problem where I want to replace the domain part in a column
with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
tip you gave Tiffany but nothing happens.
Any clues?
Regards,
Bosse
"Steve Kass" wrote:
> Tiffany,
> It sounds like what you want is
> update test2 set
> model = 'WM' + model
> where model = 'FX%'
> This will paste WM on the beginning of all the FX... model names.
> Steve Kass
> Drew University
> Tiffany wrote:
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8241
On Wed, 16 Feb 2005 07:09:06 -0800, Bosse wrote:
>Steve!
>I have a similar problem where I want to replace the domain part in a column
>with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
>tip you gave Tiffany but nothing happens.
>Any clues?
>Regards,
>Bosse
Hi Bosse,
Steve's message to Tiffany was about how to put some extra characters in
front of existing string data. To replace a part of existing string data,
you use the REPLACE function instead.
UPDATE MyTable
SET Email = REPLACE (Email, '@.xxx.se', '@.yyy.se')
WHERE Email LIKE '%@.xxx.se'
Note the extra @. and .se inserted in the replace, to make sure that an
address like aaxxxt@.xxx.se is not accidentaly changed to aayyyt@.yyy.se.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
How to insert an image into a table?..is it possible to insert?
I have a created a table pub_info with two fields 1.pub_id string 2.logo
image ...now i want to insert images into logo column and i want to retreive
that into my c#.net program...How can i do that'..please helpYou want to start here:
http://support.microsoft.com/defaul...kb;en-us;317016
http://support.microsoft.com/defaul...kb;en-us;309158
http://support.microsoft.com/defaul...kb;en-us;317043
http://support.microsoft.com/defaul...kb;en-us;317701
-oj
"saroja" <saroja@.discussions.microsoft.com> wrote in message
news:02231950-4493-4F6E-9126-E866337BB032@.microsoft.com...
>I have a created a table pub_info with two fields 1.pub_id string 2.logo
> image ...now i want to insert images into logo column and i want to
> retreive
> that into my c#.net program...How can i do that'..please helpsql
image ...now i want to insert images into logo column and i want to retreive
that into my c#.net program...How can i do that'..please helpYou want to start here:
http://support.microsoft.com/defaul...kb;en-us;317016
http://support.microsoft.com/defaul...kb;en-us;309158
http://support.microsoft.com/defaul...kb;en-us;317043
http://support.microsoft.com/defaul...kb;en-us;317701
-oj
"saroja" <saroja@.discussions.microsoft.com> wrote in message
news:02231950-4493-4F6E-9126-E866337BB032@.microsoft.com...
>I have a created a table pub_info with two fields 1.pub_id string 2.logo
> image ...now i want to insert images into logo column and i want to
> retreive
> that into my c#.net program...How can i do that'..please helpsql
Monday, March 26, 2012
How to insert a row in a table with 1 identity column
HI,
I have an SQL Server table with only 1 column. That column is an
identity column. How can I insert a row in this table using SQL
syntax?
I tried insert
into T_tableName () values ()
and a few other options, but I can't seem to get it to insert.
Thanks
Alain"Alain Filiatrault" <alainf@.humaprise.com> wrote in message
news:45d95216.0312230604.328d0c8a@.posting.google.c om...
> HI,
> I have an SQL Server table with only 1 column. That column is an
> identity column. How can I insert a row in this table using SQL
> syntax?
> I tried insert
> into T_tableName () values ()
> and a few other options, but I can't seem to get it to insert.
> Thanks
> Alain
CREATE TABLE T
(
col INT IDENTITY NOT NULL PRIMARY KEY
)
INSERT INTO T
DEFAULT VALUES
INSERT INTO T
DEFAULT VALUES
SELECT col FROM T
col
1
2
Regards,
jag
How to insert a node, with value queried from another node
We're trying to move some of our data between nodes in an XML column.
I basically want to migrate:
FROM
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
TO
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
<NewNode>1234</NewNode>
I was trying something like (which doesn't work):
UPDATE MyTable
SET XmlColumn.modify('
insert <NewNode>(/OldNodeParent/OldNode)[1]</NewNode> as last
into (/)[1]
')
WHERE XmlColumn.exist('/OldNodeParent/OldNode') = 1
AND XmlColumn.exist('/NewNode') = 0
Any ideas on how this could be achieved?
Thanks,
LubdhaTry this
UPDATE MyTable
SET XmlColumn.modify('
insert
if (count(/NewNode) = 0)
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||More correctly
UPDATE MyTable
SET XmlColumn.modify('
insert
if ( (/OldNodeParent/OldNode/text()) and not (/NewNode) )
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||Wow! That worked like a charm, thanks a lot!
I basically want to migrate:
FROM
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
TO
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
<NewNode>1234</NewNode>
I was trying something like (which doesn't work):
UPDATE MyTable
SET XmlColumn.modify('
insert <NewNode>(/OldNodeParent/OldNode)[1]</NewNode> as last
into (/)[1]
')
WHERE XmlColumn.exist('/OldNodeParent/OldNode') = 1
AND XmlColumn.exist('/NewNode') = 0
Any ideas on how this could be achieved?
Thanks,
LubdhaTry this
UPDATE MyTable
SET XmlColumn.modify('
insert
if (count(/NewNode) = 0)
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||More correctly
UPDATE MyTable
SET XmlColumn.modify('
insert
if ( (/OldNodeParent/OldNode/text()) and not (/NewNode) )
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||Wow! That worked like a charm, thanks a lot!
How to insert a new unique INT if none is given
I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?
And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like.
Thanks.
In Oracle, you would set up a sequence and ask it for the next number. Piece of cake!
I can think of two ways to do it in Sql Server.
The first is the easiest and, to my mind, the best. Create a Threads table with a ThreadId column that is an identity column. Create a trigger on your Messages table. It's simple, it's clean, and it's the right thing to do. :)
The second is to write a trigger that queries the messages table and returns the highest threadid value you find (+ 1). The problem is that, in a multi-user environment, you will have to lock the entire Messages table first to prevent anyone else from running that query until your insert finishes. Otherwise, you will get two different, unrelated messages with the same thread id. It's nasty, prone to error, and actually harder than doing the right thing.
How to insert a datetime into sql sever
Hi everyone,
How do you insert this string value lable1.text="2006-08-30 09:00:00" into a data column like startdate (type: datetime) in sql sever?
How do I convert this string value before I insert it into sql sever?
Thank you very much.
a123.
Here is a quick sample:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn2000"].ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tbl_dt (dt) SELECT @.dt", conn);
cmd.Parameters.Add("@.dt", SqlDbType.DateTime).Value = DateTime.Parse(TextBox1.Text);
cmd.ExecuteNonQuery();
}
How to insert a column in Excel by using OleDB
Hi all, I need to add a new column in an excel file, as I jus know the
query to add a new row in excel.
string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
" values ('"+name+"', '"+id+"')";
Anyone know the query to add a new column? Thankz...
Message posted via http://www.webservertalk.comYou might try the ALTER TABLE command. Not sure if it will work for a
linked Excel file, but it's standard SQL DDL to modify your table
structure - including adding a column.
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
> --
> Message posted via http://www.webservertalk.com|||Ya...but I duno the syntax of the ALTER TABLE....I have tried
string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
but it doesn't seems to work...can you please give an example of the Alter
Table SQL? Thankx...
Message posted via http://www.webservertalk.com|||> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
In SQL Server, you should omit COLUMN. I don't know if this will work with
Excel:
string strCom = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:14c9b951bfd14ffc93557f7c6d03b877@.SQ
webservertalk.com...
> Ya...but I duno the syntax of the ALTER TABLE....I have tried
> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
> but it doesn't seems to work...can you please give an example of the
> Alter
> Table SQL? Thankx...
> --
> Message posted via http://www.webservertalk.com|||I'm using C# with OleDB, here is my code
========================================
===============================
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
fileName + ";Extended Properties=Excel 8.0" ; ;
OleDbConnection myConn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
myConn.Open();
myCommand.Fill (myDataSet, "[Sheet1$]");
myConn.Close();
========================================
===============================
I got an error of "Invalid operation"...anyway, it works fine with
UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
ALTER TABLE syntax?
Message posted via http://www.webservertalk.com|||As I said, I'm not even sure ALTER TABLE is supported when accessing XLS
files via OLEDB. That was just something to try based on standard SQL DDL.
Here's a link that has a tip, although they say it's a little complex:
http://www.eggheadcafe.com/ng/micro...490.asp
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||> anything wrong with the ALTER TABLE syntax?
This syntax is valid for Microsoft SQL Server. I don't know what syntax Jet
expects or if it is even possible to add a column to an existing sheet using
the Jet OleDb provider
You might try posting your question to the OleDb forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
>
are you forced to use "oledb"?
object automation is very flexible way of interfacing excel:
set oxls=createobject("excel.application")
set owbk=oxls.workbooks.open("mytable.xls")
set owsht=owbk.activesheet
owsht.activecell.entirecolumn.insert
...
with such object you may use any construct supported by excel macro.|||Thanks for the suggestion, so how could I know wherether the ALTER TABLE is
supported or not?
Message posted via http://www.webservertalk.com|||Ops...I just saw the ODBC and JDBC...may I know where is the OleDB forum ?
Thankz.
Message posted via http://www.webservertalk.com
query to add a new row in excel.
string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
" values ('"+name+"', '"+id+"')";
Anyone know the query to add a new column? Thankz...
Message posted via http://www.webservertalk.comYou might try the ALTER TABLE command. Not sure if it will work for a
linked Excel file, but it's standard SQL DDL to modify your table
structure - including adding a column.
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
> --
> Message posted via http://www.webservertalk.com|||Ya...but I duno the syntax of the ALTER TABLE....I have tried
string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
but it doesn't seems to work...can you please give an example of the Alter
Table SQL? Thankx...
Message posted via http://www.webservertalk.com|||> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
In SQL Server, you should omit COLUMN. I don't know if this will work with
Excel:
string strCom = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:14c9b951bfd14ffc93557f7c6d03b877@.SQ
webservertalk.com...
> Ya...but I duno the syntax of the ALTER TABLE....I have tried
> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
> but it doesn't seems to work...can you please give an example of the
> Alter
> Table SQL? Thankx...
> --
> Message posted via http://www.webservertalk.com|||I'm using C# with OleDB, here is my code
========================================
===============================
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
fileName + ";Extended Properties=Excel 8.0" ; ;
OleDbConnection myConn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
myConn.Open();
myCommand.Fill (myDataSet, "[Sheet1$]");
myConn.Close();
========================================
===============================
I got an error of "Invalid operation"...anyway, it works fine with
UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
ALTER TABLE syntax?
Message posted via http://www.webservertalk.com|||As I said, I'm not even sure ALTER TABLE is supported when accessing XLS
files via OLEDB. That was just something to try based on standard SQL DDL.
Here's a link that has a tip, although they say it's a little complex:
http://www.eggheadcafe.com/ng/micro...490.asp
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||> anything wrong with the ALTER TABLE syntax?
This syntax is valid for Microsoft SQL Server. I don't know what syntax Jet
expects or if it is even possible to add a column to an existing sheet using
the Jet OleDb provider
You might try posting your question to the OleDb forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
>
are you forced to use "oledb"?
object automation is very flexible way of interfacing excel:
set oxls=createobject("excel.application")
set owbk=oxls.workbooks.open("mytable.xls")
set owsht=owbk.activesheet
owsht.activecell.entirecolumn.insert
...
with such object you may use any construct supported by excel macro.|||Thanks for the suggestion, so how could I know wherether the ALTER TABLE is
supported or not?
Message posted via http://www.webservertalk.com|||Ops...I just saw the ODBC and JDBC...may I know where is the OleDB forum ?
Thankz.
Message posted via http://www.webservertalk.com
How to insert a byte() to a blob column?
Hi everybody,
SqlDbCommand cmd = new SqlDbCommand("Insert table1(id, dataByte) values(@.id , @.data)", sqlConn);
cmd.Parameters["@.id"].ParameterValue = id;
cmd.Parameters["@.data"].ParameterValue = byteData;
int iRes = cmd.ExecuteNonQuery()
byte[] bytes = null;
/* first get the size. . . */
int num = MySqlDataReader.GetBytes("myBlobField", 0, null, 0, int.MaxValue);
if (num != 0)
{
/* allocate the bytes */
bytes = new byte[num];
/* load the bytes */
MySqlDataReader.GetBytes("myBlobField", 0, bytes, 0,num)
}
I don't where to asked this question in this forum. How do you insert to a column of a table with a blob or binary datatype if there is one, from a byte() datatype? What is wrong? How can I fixed this? I need help. Thanks.
Code:
string sqlText = "Insert table1(id, dataByte) values('" + id + "'," + byteData + ")";
OdbcCommand cmd = new OdbcCommand(sqlText, odConn);
//opening connection here
int iRes = cmd.ExecuteNonQuery();
Result:
iRes = -1
den2005parameterize, parameterize, parameterize
read up on parameters
on a side note - never build sql!
on another side note. . . don't use odbc (there are bugs in the MDAC sql odbc driver)
use oledb. . .
better yet - use the sqlclient library
untested code (might have missed a particular point but this is the gist):
SqlDbCommand cmd = new SqlDbCommand("Insert table1(id, dataByte) values(@.id , @.data)", sqlConn);
cmd.Parameters["@.id"].ParameterValue = id;
cmd.Parameters["@.data"].ParameterValue = byteData;
int iRes = cmd.ExecuteNonQuery()
Research "how to store an image in a database" - its the same concept.
Again. . . parameterize your queries.
Insist that your peers do the same.
Security - Performance - Maintainence
and this question belongs in .Net Data Access Forum|||Thanks for reply, Blair Allen. I solved this my problem now is retrieving this BLOB from database and converting it to byte() and loading it to a Micorosft.Ink object using Ink.Load() method. The error occurs at Ink.Load() statement. Can anyone help? Thanks for advise. I'll post this problem at .Net Data Access Forum.
den2005|||I think this is it:
just hacked, not checked
byte[] bytes = null;
/* first get the size. . . */
int num = MySqlDataReader.GetBytes("myBlobField", 0, null, 0, int.MaxValue);
if (num != 0)
{
/* allocate the bytes */
bytes = new byte[num];
/* load the bytes */
MySqlDataReader.GetBytes("myBlobField", 0, bytes, 0,num)
}
cheers|||Thanks Blair Allen for reply I used a different approach. I converted the byte() to a base64 string format and then store it as a Text data in database and retrieving it as string and used Convert.FromBase64String() method to convert it back to byte() and load it to Ink.Load() and it works.
den2005
How to inquire on column headings?
Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.There is a MS Access forum on this site. The below should help you out:
http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names
Originally posted by masha
Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.|||Originally posted by dmmac
There is a MS Access forum on this site. The below should help you out:
http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names
Thank you very much! That solves it indeed.
Sorry for posting in this forum. I did it because I hoped that it were possible using pure SQL.
Thanks again!sql
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.There is a MS Access forum on this site. The below should help you out:
http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names
Originally posted by masha
Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.|||Originally posted by dmmac
There is a MS Access forum on this site. The below should help you out:
http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names
Thank you very much! That solves it indeed.
Sorry for posting in this forum. I did it because I hoped that it were possible using pure SQL.
Thanks again!sql
How to index through columns
How do you index through a set of Columns Programmatically using SQL. I have a Table all the columns have the same data for different dates. Column names are Col_0, Col_1, Col_2,....Col_100, Col_101. I need to perform the same calculation on each column to manipulate the data into a different table. Is it possible to do a While loop that changes the Column name in a SELECT statement. I have tried to do this but can't seem to get it to work. Please help !
You could set up a cursor using syscolumns.
Run this in QA after changing the table name:
select colid, name
from syscolumns
where id = (
select id
from sysobjects
where name = 'MyTableName')
order by colid
That should give you an idea of how to use that. You can dynamically build SQL using your cursor and execute it. There may be something more elegant, but I've used this successfully in the past.
|||Thank PDraigh I will try it
Subscribe to:
Posts (Atom)