Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts

Monday, March 26, 2012

jtds blob size

When I try to insert more then 200Mb data into blob field (varbinary) using jtds jdbc-driver I've got an error. Connection is going closed. In fact I cant insert so much data with other tools too (TOAD for MSSQL).Could you try Microsoft JDBC driver for SQLServer 2005? This may work better. In fact we are planning a v 1.1 release this August. This release can handle larger datatypes better.

Monday, March 19, 2012

Joining Table Operation

I have two tables Customer and Customer Details.
Both are joinde with cust_id.
How can I insert data both the tables at the same time using ado.net.
Should I insert individually or can I use JOIN operation for insertion.
Please correct me with an example would be really appreciable.
Thanks in advanceYOu can′t do a multi-insert in a table, you have to wrap these two statement in a transaction and execute / commit these two statements together.

HTH, Jens Suessmeyer.|||Note that the best way to achieve this is by performing both inserts in a transaction. And this is the simplest method to solve this problem. SQL Server 2000/2005 also has INSTEAD OF TRIGGERS that can be used to say update a complex read-only view. With this approach you will simply insert/update/delete from the view & the trigger logic can be perform the necessary DML operations on the base tables. This approach however is overkill for this particular problem.|||Just in addition, transactions are very costly on the server side, so use them wisely.

HTH, Jens Suessmeyer.

Friday, February 24, 2012

Join Question

Hi
I drop table employee
go
create table employee
( fname char(20),
lname char(36),
dept char(6),
in_dt char(6)
)
insert into employee values ('Joe','Doe','legal','980622')
insert into employee values ('Joe','Doe','legal','990313')
insert into employee values ('Joe','Doe','legal','990704')
insert into employee values ('Joe','Doe','legal','991015')
insert into employee values ('Joe','Doe','legal','000329')
insert into employee values ('Joe','Doe','legal','010503')
drop table work_day
go
create table work_day
(fname char(20),
lname char(36),
dept char(6),
out_dt char(6)
)
insert into work_day values ('Joe','Doe','legal','990228')
insert into work_day values ('Joe','Doe','legal','000617')
insert into work_day values ('Joe','Doe','legal','010407')
select c.fname,
c.lname,
c.dept,
c.in_dt,
e.out_dt
from employee c
left join work_day e
on c.fname = e.fname
and c.lname=e.lname
and c.dept = e.dept
and convert(datetime,c.in_dt) < convert(datetime,e.out_dt)
go
I am getting the multiple records
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 980622
000617
Joe Doe legal 980622
010407
Joe Doe legal 990313
000617
Joe Doe legal 990313
010407
Joe Doe legal 990704
000617
Joe Doe legal 990704
010407
Joe Doe legal 991015
000617
Joe Doe legal 991015
010407
Joe Doe legal 000329
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
and I need the following output
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
Any Suggestions
AjHello,
Thank you for including DDL, sample data and expected result.
However, there are a few problems:
1. Your DDL does not include primary keys (and other constraints)
2. You use char(6) instead of datetime. That's really bad, for (at
least) two reasons:
- performance: converting the values to datetime prevents SQL Server
from using indexes
- data integrity: in a char(6) you can store a value that is not a
valid date and you won't notice until it's too late
3. The expected result... is not quite what I expected. Either the
provided expected result is be wrong or I am unable to understand what
it should contain. If the expected result would have been this:
fname lname dept in_dt out_dt
-- -- -- -- --
Joe Doe legal 980622 990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015 NULL
Joe Doe legal 000329 000617
Joe Doe legal 010503 NULL
Then a possible solution is this:
SELECT fname, lname, dept, in_dt, (
SELECT MIN(out_dt)
FROM work_day e
WHERE e.fname=c.fname and e.lname=c.lname
AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
AND NOT EXISTS (
SELECT *
FROM employee d
WHERE d.fname=c.fname and d.lname=c.lname
AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
)
) AS out_dt
FROM employee c
Razvan|||Thank you, I agree with the char date field but that is what the table was
initially created with and I am extracting data from it. Your script gave
me the output I will looking for.
Aj
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116436704.952637.73930@.g49g2000cwa.googlegroups.com...
> Hello,
> Thank you for including DDL, sample data and expected result.
> However, there are a few problems:
> 1. Your DDL does not include primary keys (and other constraints)
> 2. You use char(6) instead of datetime. That's really bad, for (at
> least) two reasons:
> - performance: converting the values to datetime prevents SQL Server
> from using indexes
> - data integrity: in a char(6) you can store a value that is not a
> valid date and you won't notice until it's too late
> 3. The expected result... is not quite what I expected. Either the
> provided expected result is be wrong or I am unable to understand what
> it should contain. If the expected result would have been this:
> fname lname dept in_dt out_dt
> -- -- -- -- --
> Joe Doe legal 980622 990228
> Joe Doe legal 990313 NULL
> Joe Doe legal 990704 NULL
> Joe Doe legal 991015 NULL
> Joe Doe legal 000329 000617
> Joe Doe legal 010503 NULL
> Then a possible solution is this:
> SELECT fname, lname, dept, in_dt, (
> SELECT MIN(out_dt)
> FROM work_day e
> WHERE e.fname=c.fname and e.lname=c.lname
> AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
> AND NOT EXISTS (
> SELECT *
> FROM employee d
> WHERE d.fname=c.fname and d.lname=c.lname
> AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
> AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
> )
> ) AS out_dt
> FROM employee c
> Razvan
>

Monday, February 20, 2012

Join problem

Hi

CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users] VALUES('Unal')
INSERT INTO [dbo].[Users] VALUES('Volkan')
INSERT INTO [dbo].[Users] VALUES('Duygu')
INSERT INTO [dbo].[Users] VALUES('Elif')
INSERT INTO [dbo].[Users] VALUES('Mehmet')
INSERT INTO [dbo].[Users] VALUES('Demir')


CREATE TABLE [dbo].[Agenda](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityName] [nvarchar](50) NULL,
[UserId] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Agenda] VALUES('school excursion','1,3,4')
INSERT INTO [dbo].[Agenda] VALUES('party','6,2,3,1')

Table Name : Users

Id UserName

1 Unal

2 Volkan

3 Duygu

4 Elif

5 Mehmet

6 Demir


Table Name : Agenda

Id ActivityName UserId

1 school excursion 1,3,4

2 party 6,2,3,1


I want to join Users table with Agenda table.

The Result has to be like below:

Id ActivityName UserId UserNames

1 school excursion 1,3,4 Unal, Duygu, Elif

2 party 6,2,3,1 Demir, Volkan, Duygu, Unal

How can I do it in SQL 2005. I don't want use cursor.

OR

if I can't do that join, I have to do other join that it's below.

Id ActivityName UserId UserNames

1 school excursion 1 Unal

1 school excursion 3 Duygu

1 school excursion 4 Elif

2 party 6 Demir

2 party 2 Volkan

2 party 3 Duygu

2 party 1 Unal

thanks so much for help

What you are attempting to accomplish is a form of denormalization. While it is not a 'straightforward' task in T-SQL, here are links to a couple of approaches that may work for you.

Lists -Field Concatenation, One Field to Itself for string
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||
Thanks everbody who help and answer.