inserted value on text field gets truncated after 255 chars

Hello,
I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
statement over a table. In the table I have two 'text' fields with the
same properties, with just one difference: one field allows nulls, the
other one does not.
Well, one field actually accepts only the first 255 chars (the nullable
field), while the other field has no problems.
The "Length" property is set to 16 for both fields, as I said all the
properties but one (null/not null) are exactly the same, and also the
context is the same (same database, same table).
Many thanks for your help!
GiovanniHow does your SP look?
It sounds like you truncate it somewhere there. Maybe the parameter is
a varchar or something like that?|||How are you validating that only 255 characters are there? Are you using
SELECT DATALENGTH(col_name) FROM table? Or are you counting the number of
characters in the result set?
"gm1974" <gmascia@.gmail.com> wrote in message
news:1138737544.776002.219580@.f14g2000cwb.googlegroups.com...
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the nullable
> field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
> Many thanks for your help!
> Giovanni
>|||gm1974 wrote:
> Hello,
> I have a SP on SQL Server 2005 (Express Ed.) which performs an INSERT
> statement over a table. In the table I have two 'text' fields with the
> same properties, with just one difference: one field allows nulls, the
> other one does not.
> Well, one field actually accepts only the first 255 chars (the
> nullable field), while the other field has no problems.
> The "Length" property is set to 16 for both fields, as I said all the
> properties but one (null/not null) are exactly the same, and also the
> context is the same (same database, same table).
>
Are you testing it in QA? If so, you should modify the "maximum characters
per column" setting in the QA options dialog.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Oh, I must be really tired. I definitely forgot to change parameter
type in the SP, it was still set at VarChar(255), so the value was
truncated!
Better to get some sleep, many thanks for your help.|||Thanks for your help, it may be useful in the future.
Giovanni

insert/update trigger

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@.work_ord_num = work_ord_num , @.work_ord_line_num = work_ord_line_num

Thanks for your help!tbl2 and tbl3 should be joined with inserted.

Insert values into 2 tables with one INSERT

Is this the appropriate way to insert information into a database with 2 tables (related with empID fields)

INSERT INTO table1
empID, first, last, dept, district
INSERT INTO table 2
className, classType, classDate

I am using ColdFusion to send the information to the database.

Thanks!Originally posted by helios76
Is this the appropriate way to insert information into a database with 2 tables (related with empID fields)

INSERT INTO table1
empID, first, last, dept, district
INSERT INTO table 2
className, classType, classDate

I am using ColdFusion to send the information to the database.

Thanks!

uhhhh...no

post the ddl for your tables...syntax is

INSERT INTO myTable (col1, col2, ect)
SELECT col1, col2, ect
FROM SomeOtherTable|||OK, full story of what I am trying to do.
Create a form that will go through ColdFusion to the database. This is how CFMX uses the SQL commands:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

The #Form.Emp_ID# is how CFMX knows what was entered on the HTML form and then sends it to the database.

What my goal is, is to send data to 2 tables that are linked by Emp_ID in the same database.|||Originally posted by helios76
OK, full story of what I am trying to do.
Create a form that will go through ColdFusion to the database. This is how CFMX uses the SQL commands:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

The #Form.Emp_ID# is how CFMX knows what was entered on the HTML form and then sends it to the database.

What my goal is, is to send data to 2 tables that are linked by Emp_ID in the same database.

You could add another insert in batch like this:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
INSERT INTO Employee2
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

Insert using multiple field terminators

Hi all,
In Oracle, SQL Loader allows you to use the statement below:
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
Is there a way to do this with BULK INSERT or some other import function?
The data files that I am attempting to import has fields terminated by commas
but when some of those text fields may contain a comma, they are also
enclosed by pipes. The above statement in Oracle deals with this issue
effectively... hoping I can find something for MS SQL Server. Thanks!
PatrickPatrick
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
stFName stLName stEmail
-- -- --
Kelly Reynold kelly@.reynold.com
John Smith bill@.smith.com
Sara Parker sara@.parker.com
drop table TmpStList
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:CA167741-DE9E-4752-AEE1-97D3A67C3837@.microsoft.com...
> Hi all,
> In Oracle, SQL Loader allows you to use the statement below:
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
> Is there a way to do this with BULK INSERT or some other import function?
> The data files that I am attempting to import has fields terminated by
> commas
> but when some of those text fields may contain a comma, they are also
> enclosed by pipes. The above statement in Oracle deals with this issue
> effectively... hoping I can find something for MS SQL Server. Thanks!
> Patrick
>

Insert unicode data into the database with Typed DataSet

Hi all,

I am using aStrongly Typed DataSet (ASP.NET 2.0) to insert new data into aSQL Server 2000 database, types of some fields in db arenvarchar.

All thing work fine except I can not insertunicode data(Vietnamese language) into db.

I can't find where to putprefix N.

Please help me!!!

The last collation definition is the MSDN link below is for Vietnamese_CI_AS you have to use that as your database and column collation because 2000 support column level collation. So use NChar, NVarchar and NText as your data types and use the collation below. VS2005 also comes with the option to choose the editor to use in saving your files and the correct encoding so use Vietnamese encoding to save your dataset file, that is covered in the second link. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms143508.aspx

https://www.microsoft.co.ke/middleeast/msdn/arabicsupp.aspx#7

Insert Trigger Question

Can someone help me with this scenario:
I have 2 tables one detail and one summary. The key fields for betwen the the detail and summary tables are the date field na the part # field.
I need to create a trigger that will insert records when they do not exist in the summary table and also only update the records that are needing to be modified. Any Ideas?

ThanksReal time warehousing?

Can't you run a scheduled process instead?|||Originally posted by Brett Kaiser
Real time warehousing?

Can't you run a scheduled process instead?

I need to have this summary infomration available at any time the daily reports need to be run. A scheduled process would be easier but I need to keep this table up to date as transcations are processed in the detail table.|||Use this as an example:

create table item(id int identity,item# varchar(10))
create table itemsummary(id int identity,item# varchar(10),quantity int)
go
create trigger iu_item on item
for insert,update
as
insert itemsummary(item#)
select item# from inserted i
where not exists(select 1 from itemsummary where item#=i.item#)
update itemsummary set quantity=(select count(*) from item i where i.item#=itemsummary.item#)
go
insert item(item#) values('#1')
insert item(item#) values('#1')
insert item(item#) values('#2')

select * from item
select * from itemsummary|||He'll need to update..the count? for the part#

So you need 2 sections...

Check

-- An Update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)

--An INSERT
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)

Then do your apporpriate action...

What's the transaction level?sql

Insert Trigger How to work

Hi All!

I have a table which has fields |person_id| AND |fullname|

I would like to create and insert trigger so that no duplicate names shoudl be inserted, I tryed Following but am geeting confused Where I am going Wrong

I need to take an Input from user to this trigger as well how is it possiible??

create trigger ins_per
on persons
for insert as
Declare @.p_name varchar(45)
select * from persons where full_name=@.p_name
if @.@.rowcount>0
Begin
Print
'Sorry'
ROLLBACK TRANSACTION
EndA unique constraint does the trick!

Alter table x add constraint constraint_name unique nonclustered (Column 1,Column 2)

The combination column1 + column2 must be unique.

Hope it helps!sql

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up
4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank you
On Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:
>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Insert trigger changing record

Hello all!
I want to create an insert trigger to change some fields of the inserted
record. I want to put in two fields the system date and system time.
When I try to update Inserte table I get an error telling me I cannot
update inserted tables.
Can anyone give me a hand on this?
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
update Inserted
set CreationDate = dbo.idlog_date(current_timestamp)
--
Function idlog_date returns the date in my format.
Thanks in advance,
Hugo MadureiraHugo Madureira wrote:
> Hello all!
> I want to create an insert trigger to change some fields of the inserted
> record. I want to put in two fields the system date and system time.
> When I try to update Inserte table I get an error telling me I cannot
> update inserted tables.
> Can anyone give me a hand on this?
>
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
UPDATE dbo.transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
GO
It seems like overkill to use a trigger for this. Have you considered
declaring a DEFAULT value instead: DEFAULT CURRENT_TIMESTAMP.

> Function idlog_date returns the date in my format.
A DATETIME column doesn't have a "format". Why store the date as
anything other than DATETIME or SMALLDATETIME?
David Portas
SQL Server MVP
--|||Yes, thats right. You have to update the original data which is already
store in there.
UPDATE Transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
FROM Transactions T
INNER JOIN INSERTED I
ON T.<YourprimaryKey> = I.<YourprimaryKey>
HTH, Jens Suessmeyer

Insert trigger

Hi

I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
Turnover, VAT, Netturnover). I get a file which I have to import every
know and then, with new data. In this file I only get values for (ID,
Date, Turnover and VAT). The import is working fine with the import
wizard.

The problem is, that I want to have the Netturnover computed at the
time of insert to equal [Turnover-VAT], but I don't really know how to
as I'm new to these triggers.

Could anyone help me I would appriciate this.
BR / Janjazpar (jannoergaard@.hotmail.com) writes:
> I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> Turnover, VAT, Netturnover). I get a file which I have to import every
> know and then, with new data. In this file I only get values for (ID,
> Date, Turnover and VAT). The import is working fine with the import
> wizard.
> The problem is, that I want to have the Netturnover computed at the
> time of insert to equal [Turnover-VAT], but I don't really know how to
> as I'm new to these triggers.

The simplest is to make NetTurnover a computed column:

CREATE TABLE DebtorTurnover
(ID int NOT NULL,
Date datetime NOT NULL,
Turnover decimal(10,2) NOT NULL,
VAT decimal(10, 2) NOT NULL,
Netturnover AS Turnover - VAT)

A trigger would look like this:

CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
FOR INSERT, UPDATE AS
UPDATE DebtorTurnover
SET Netturnover = dt.Turnover - dt.VAT
FROM DebtorTurnover dt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.ID = i.ID

The "inserted" table is a virtual table that holds the inserted rows,
or in case of an UPDATE, the update rows after the table.

--
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.mspx|||Erland Sommarskog skrev:

> jazpar (jannoergaard@.hotmail.com) writes:
> > I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> > Turnover, VAT, Netturnover). I get a file which I have to import every
> > know and then, with new data. In this file I only get values for (ID,
> > Date, Turnover and VAT). The import is working fine with the import
> > wizard.
> > The problem is, that I want to have the Netturnover computed at the
> > time of insert to equal [Turnover-VAT], but I don't really know how to
> > as I'm new to these triggers.
> The simplest is to make NetTurnover a computed column:
> CREATE TABLE DebtorTurnover
> (ID int NOT NULL,
> Date datetime NOT NULL,
> Turnover decimal(10,2) NOT NULL,
> VAT decimal(10, 2) NOT NULL,
> Netturnover AS Turnover - VAT)
> A trigger would look like this:
> CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
> FOR INSERT, UPDATE AS
> UPDATE DebtorTurnover
> SET Netturnover = dt.Turnover - dt.VAT
> FROM DebtorTurnover dt
> WHERE EXISTS (SELECT *
> FROM inserted dt
> WHERE dt.ID = i.ID
> The "inserted" table is a virtual table that holds the inserted rows,
> or in case of an UPDATE, the update rows after the table.
Hi Thanks for you reply

I made the following

Table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DepTurnOver]
GO

CREATE TABLE [dbo].[DepTurnOver] (
[Year] [int] NULL ,
[Week] [int] NULL ,
[CalleId] [int] NULL ,
[ShopId] [int] NULL ,
[ItemGroupId] [int] NULL ,
[TurnOver] [real] NULL ,
[Discount] [real] NULL ,
[Qty] [real] NULL ,
[Customer] [int] NULL ,
[VAT] [real] NULL ,
[Consumption] [real] NULL,
[Netturnover] AS [Turnover]-[VAT]
) ON [PRIMARY]
GO

Trigger:
CREATE TRIGGER DepTurnover_tri ON DepTurnover
FOR INSERT, UPDATE AS
UPDATE DepTurnover
SET Netturnover = idt.Turnover - idt.VAT
FROM DepTurnover idt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.Year = idt.Year
AND dt.Week = idt.week
AND dt.CalleId = idt.CalleId
AND dt.ShopId = idt.ShopId
AND dt.ItemGroupId = idt.ItemGroupId)

But when I try to save the trigger I get the following error:
Server: Msg 271, Level 16, State 1, Procedure DepTurnover_tri, Line 3
Column 'Netturnover' cannot be modified because it is a computed
column.

Have I done anything wrong here.

Thanks in advance
BR/ Jan

> --
> 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.mspx|||jazpar (jannoergaard@.hotmail.com) writes:
> I made the following
> Table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[DepTurnOver]
> GO
> CREATE TABLE [dbo].[DepTurnOver] (
> [Year] [int] NULL ,
> [Week] [int] NULL ,
> [CalleId] [int] NULL ,
> [ShopId] [int] NULL ,
> [ItemGroupId] [int] NULL ,
> [TurnOver] [real] NULL ,
> [Discount] [real] NULL ,
> [Qty] [real] NULL ,
> [Customer] [int] NULL ,
> [VAT] [real] NULL ,
> [Consumption] [real] NULL,
> [Netturnover] AS [Turnover]-[VAT]
> ) ON [PRIMARY]
> GO
> Trigger:

Sorry, I was a bit brief. If you have a computed column, you don't
need the trigger at all. I included the trigger code, in case you
were not in position to change the table definition.

--
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.mspx

INSERT Statement Question

Hello everyone,
I have a general question about INSERT statements. Is it possible to
insert fields into a table both through a SELECT statement and
explicitly in the same INSERT statement? For example, if I have the
following part of an INSERT statement:
INSERT INTO table1 (column1, column2)
Is it possible to insert "column1" using a SELECT statement and
"column2" explicitly? What I am trying to do is insert multiple rows
into a table where some of the field information I need is located in
another table and some I need to insert explicitly. Thanks for your
replies in advance.This would work..
INSERT INTO table1 (column1, column2)
select au_lname, au_fname from authors
UNION
SELECT 'Fred','Bloggs'
HTH. Ryan
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||>Is it possible to insert "column1" using a SELECT statement and
>"column2" explicitly?
Yes
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||I think i mis-understood.
Try this :-
DECLARE @.Constant varchar(50)
SET @.Constant = 'Constant Value'
INSERT INTO table1 (column1, column2)
SELECT Col001, @.Constant FROM tablename
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23K9KYfijGHA.4044@.TK2MSFTNGP03.phx.gbl...
> This would work..
> INSERT INTO table1 (column1, column2)
> select au_lname, au_fname from authors
> UNION
> SELECT 'Fred','Bloggs'
> --
> HTH. Ryan
>
> "schoultzy" <schoultzy@.yahoo.com> wrote in message
> news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
>|||Thanks to everyone for your replies. It seams as though using a
variable to hold the constant information will be the best solution for
me. Thank you for holding my hand. My novice status with SQL was on
full display with this post.|||No worries, we all need to start somewhere.
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150121330.164136.315130@.h76g2000cwa.googlegroups.com...
> Thanks to everyone for your replies. It seams as though using a
> variable to hold the constant information will be the best solution for
> me. Thank you for holding my hand. My novice status with SQL was on
> full display with this post.
>

insert statement help

hi,
i have a small question regarding sql, there are two tables that i need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names that i need to insert into table1, i'm using sql server, is there a way to insert them into table1?? i'm not sure how to write a query within the insert statment to get them inserted into table1? something like:
insert into table(id, name, street, zip, phone, fax, ...) values(newid(), (select distinct name from table2), null, null, null...)
and is there a way to do it without all the nulls having to be put in, there are about 20 more columns in table1, and id in table1 is unique.insert into table1 ( name)
select distinct name from table2

rudy

insert statement help

hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more
columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names
that i need to insert into table1, i'm using sql server, is there a
way to insert them into table1?? i'm not sure how to write a query
within the insert statment to get them inserted into table1?
something like:
insert into table(id, name, street, zip, phone, fax, ...)
values(newid(), (select distinct name from table2), null, null,
null...)
and is there a way to do it without all the nulls having to be put in,
there are about 20 more columns in table1, and id in table1 is unique.[posted and mailed, please reply in news]

soni29 (soni29@.hotmail.com) writes:
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.

Your question is a bit vague, and since I don't see the tables, nor do
I see the data, I have to guess.

If all you want to is to insert the disctinct names in table2 into table1,
without providing any values for the other columns, save the id column,
this is the statement:

INSERT table1 (id, name)
SELECT disctint newid(), name FROM table2

Thus, you do need to list a column in the column list of the INSERT
statement, if you wish to set it to NULL or its default value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Check out the insert syntax in books online (use the Go/URL menus!):

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_md_03_1kz8.htm

If the columns are nullable and don't have a value or if the are not
nullable and take the default then you do not have to mention them in the
select statement. If they are nullable then the DEFAULT keyword can be used.

To create a default for your id column then it can be declare with a default
see:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_na-nop_4pt0.htm

i.e.

CREATE TABLE cust
(
id uniqueidentifier NOT NULL
DEFAULT newid(),
....

)
GO

Therefore you can do something like:

insert into table(name, street, zip, phone, fax)
select distinct name, street, zip, phone, fax from table2

If you can not get distinct from this then you may need a subquery such as:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_11_3smm.htm

John

"soni29" <soni29@.hotmail.com> wrote in message
news:cad7a075.0312140855.7c8b2475@.posting.google.c om...
> hi,
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9451C9DBAF97CYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> soni29 (soni29@.hotmail.com) writes:
> > i have a small question regarding sql, there are two tables that i
> > need to work with on this, one has fields like:
> > Table1:
> > (id, name, street, city, zip, phone, fax, etc...) about 20 more
> > columns
> > Table2:
> > name
> > what i need help with is that table2 contains about 200 distinct names
> > that i need to insert into table1, i'm using sql server, is there a
> > way to insert them into table1?? i'm not sure how to write a query
> > within the insert statment to get them inserted into table1?
> > something like:
> > insert into table(id, name, street, zip, phone, fax, ...)
> > values(newid(), (select distinct name from table2), null, null,
> > null...)
> > and is there a way to do it without all the nulls having to be put in,
> > there are about 20 more columns in table1, and id in table1 is unique.
> Your question is a bit vague, and since I don't see the tables, nor do
> I see the data, I have to guess.
> If all you want to is to insert the disctinct names in table2 into table1,
> without providing any values for the other columns, save the id column,
> this is the statement:
> INSERT table1 (id, name)
> SELECT disctint newid(), name FROM table2
> Thus, you do need to list a column in the column list of the INSERT
> statement, if you wish to set it to NULL or its default value.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

A minor correction - the syntax above will generate a new uniqueidentifier
for each row in the source table before applying the DISTINCT, so you will
get all the values from the source table anyway. Something like this should
work correctly:

insert into table1 (id, name)
select newid(), name from
(
select distinct name
from table2 ) dt

Although as you pointed out, without seeing data and DDL, it's not at all
clear what 'correctly' means here, so my version may not be what the poster
wants either.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> A minor correction - the syntax above will generate a new uniqueidentifier
> for each row in the source table before applying the DISTINCT, so you will
> get all the values from the source table anyway.

Oops!

Thanks for the correction, Simon!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Insert Statement

I have an insert statement with 2 tables, such as:
Insert tForm Select * From tForm2
I know that tForm has all the fields in tForm2, but tForm2 may have some
additional fields not in tForm. The insert statement will not work because
the same # of columns do not exist in both tables. Is there a way to do
this without going through the task of building dynamic sql to determine
which fields do exist in the tables?
Derek HartWhy does it have to by dynamic SQL? Are these tables really changing that
often?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:eixLbP9bGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I have an insert statement with 2 tables, such as:
> Insert tForm Select * From tForm2
> I know that tForm has all the fields in tForm2, but tForm2 may have some
> additional fields not in tForm. The insert statement will not work
> because the same # of columns do not exist in both tables. Is there a way
> to do this without going through the task of building dynamic sql to
> determine which fields do exist in the tables?
> Derek Hart
>|||I have 150 tables in a development database. I send new versions out into
production; there could be 200 versions out there. The development database
will never remove tables or fields, but the production versions may not have
all the fields in a given table. I have been working with DTS to move data
between systems, but if the tables do not have the exact same fields, I
cannot use a statement such as:
Insert Database1.tForm Select * From Database2.tForm
I could do this in DTS or in a stored procedure, but I cannot determine
solutions for either of them. In DTS, the transformations must be built
dynamically because they do not simply auto-map on their own. Any thoughts
on this?
Derek Hart
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Why does it have to by dynamic SQL? Are these tables really changing that
> often?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Derek Hart" <derekmhart@.yahoo.com> wrote in message
> news:eixLbP9bGHA.4900@.TK2MSFTNGP02.phx.gbl...
>|||You might have to look into versioning these databases, instead of trying to
do this in an ad hoc manner. Are you sure that an insert without all of the
columns, for any given table, will actually make sense?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
>I have 150 tables in a development database. I send new versions out into
>production; there could be 200 versions out there. The development
>database will never remove tables or fields, but the production versions
>may not have all the fields in a given table. I have been working with DTS
>to move data between systems, but if the tables do not have the exact same
>fields, I cannot use a statement such as:
> Insert Database1.tForm Select * From Database2.tForm
> I could do this in DTS or in a stored procedure, but I cannot determine
> solutions for either of them. In DTS, the transformations must be built
> dynamically because they do not simply auto-map on their own. Any
> thoughts on this?
> Derek Hart
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||Why not just refresh you development environment once in the while, and
create a test environment also, which would be refreshed more often?
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
> I have 150 tables in a development database. I send new versions out into
> production; there could be 200 versions out there. The development
database
> will never remove tables or fields, but the production versions may not
have
> all the fields in a given table. I have been working with DTS to move
data
> between systems, but if the tables do not have the exact same fields, I
> cannot use a statement such as:
> Insert Database1.tForm Select * From Database2.tForm
> I could do this in DTS or in a stored procedure, but I cannot determine
> solutions for either of them. In DTS, the transformations must be built
> dynamically because they do not simply auto-map on their own. Any
thoughts
> on this?
> Derek Hart
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
that
some
to
>|||I have the following code which will give me all the tables, but I will need
to dynamically generate the select list and explicitly name the fields -
here is the code I received in another post. I would be grateful if somebody
could give me the information of how I could create the list of fields in
the source table (OldDB) to dynamically insert into the destination table
(NewDB) - I know that there may be fields in the NewDB that are not in the
OldDB, but not vice versa. And I know defaults will handle any fields that
do not make it into the select list. How can this be done? By the way I
have to have this scripted onto the production machines that I do not have
direct access to.
declare @.sql varchar(8000)
declare @.table_name varchar(256)
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
DECLARE table_list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
OPEN table_list
FETCH NEXT FROM table_list INTO
@.table_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
OldDatabase..' + @.table_name + ' ) '
--print @.sql
--EXEC (@.SQL)
FETCH NEXT FROM table_list INTO
@.table_name
END
DEALLOCATE table_list
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:enL6%23DFcGHA.4896@.TK2MSFTNGP03.phx.gbl...
> Why not just refresh you development environment once in the while, and
> create a test environment also, which would be refreshed more often?
> "Derek Hart" <derekmhart@.yahoo.com> wrote in message
> news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
> database
> have
> data
> thoughts
> that
> some
> to
>

insert semi-colon

Hi. I need to insert a semicolon into one of my fields in a sql server 2000 database. I have a height column and i was trying to insert height. i know i can't insert 4'5" b/c of the apostrophe and double quote, so i was trying to insert it like: 4'7"now i'm running into a problem with the semicolons. how can i insert the semicolons? thanks!

edit:
aparently this is removing the same things: here's what i'm talking about:
http://www.thoughtreactor.com/img-0009.pnginsert into theTable (theColumn) values (';');
Where is the problem?|||Hi. I need to insert a semicolon into one of my fields in a sql server 2000 database. I have a height column and i was trying to insert height. i know i can't insert 4'5" b/c of the apostrophe and double quote, so i was trying to insert it like: 4'7"now i'm running into a problem with the semicolons. how can i insert the semicolons? thanks!

edit:
aparently this is removing the same things: here's what i'm talking about:
http://www.thoughtreactor.com/img-0009.png

Read up on escape characters in BOL.


create table #specialk (colid int identity, char_desc varchar(20), special_char varchar(10))
insert into #specialk (char_desc, special_char)
SELECT 'APOSTROPHE', ''''
UNION
SELECT 'QUOTE', '"'
UNION
SELECT 'SEMICOLON', ';'
UNION
SELECT 'Height', '4'' 7"'
SELECT * FROM #specialk order by colid
drop table #specialk
-- OUTPUT
colid char_desc special_char
---- ------- ----
1 APOSTROPHE '
2 Height 4' 7"
3 QUOTE "
4 SEMICOLON ;
(4 row(s) affected)|||i have a dropdown menu where the user selects the height. it looks like the picture i posted above. whenever it inputs the value, it says i have an error and it's near the semicolon.|||It looks like you are not inserting the data with escape characters ... go read and play with escape characters.

I've pointed you toward a solution - I'm not going to solve it for you.

I'm showing you how to fish ... not filleting it for you.|||It looks like you are not inserting the data with escape characters ... go read and play with escape characters.

I've pointed you toward a solution - I'm not going to solve it for you.

I'm showing you how to fish ... not filleting it for you.
well, i fiddled with it for a little while and had no luck so I decided to take the step and re-normalize my database and just have a separate table for the height description. but thank you for pointing me in the right direction!|||also, it might make sense for you to store height in inches only, in an int column, rather than a combination of feet and inches in a char column. Then you could easily do things like sort by height, etc.

your client app could easily convert the number 65 to 5' 5" if that's the requirement.

using a character type to store what's really a number is a bad design.|||also, it might make sense for you to store height in inches only, in an int column, rather than a combination of feet and inches in a char column. Then you could easily do things like sort by height, etc.

your client app could easily convert the number 65 to 5' 5" if that's the requirement.

using a character type to store what's really a number is a bad design.
that's exactly what i did. at first i started out with 4'7" but i thought what if we wanted to find the average height of people? so i decided to put it in inches. thanks for your help though!|||Well then you wouldn't be storing "display" data.

Either, store the height in decimal form, or use 2 columns, 1 for feet and one for inches|||I would never use two columns for the same measure, in this case a length. sorting would not work at all if someone puts 0 for the feet and 100 for the inches...

if 2 columns is good, why not 4 columns? one for hands, one for furlongs, one for fathoms, one for millimeters?

use one column, and make it a number. fine to use a decimal if you care about fractions of inches. here's a tip: include the units in the name of the column so it's crystal clear: HeightInches|||It's all about using parameterised queries/statements in your client. I'd read up on that even in preference to escape characters which you rarely need to bother with.
FWIW I agree that you should be storing inches only. Is 4'7" really less than 6"? It is if you store it as a varchar and order by height.
Personally I'd use cm ;)|||Personally I'd use cm
Me as well, or mm if it should be accurate

insert query fails (if form fields left empty)

Dear All,

I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".

As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?

<script language="VB" runat="server" >

Sub Page_Load(Src As Object, e As EventArgs)


If Page.IsPostBack Then

Dim ConLath As SqlConnection
Dim comLath As SqlCommand
Dim insertcmd

conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")
ConLath.Open()
insertcmd = "Insert into His_set values (@.t_d,@.s_p,@.p_s,@.v_oq,@.i_oq,@.v_qn,@.i_qn,@.v_qw,@.i_qw)"

comLath = New SqlCommand(insertcmd, ConLath)


comLath.Parameters.Add(New SqlParameter("@.t_d", SqlDbType.DateTime, 12))
comLath.Parameters("@.t_d").Value = trade_date.Text
comLath.Parameters.Add(New SqlParameter("@.s_p", SqlDbType.Decimal, 8))
comLath.Parameters("@.s_p").Value = sett_price.Text
comLath.Parameters.Add(New SqlParameter("@.p_s", SqlDbType.Decimal, 8))
comLath.Parameters("@.p_s").Value = post_close.Text
comLath.Parameters.Add(New SqlParameter("@.v_oq", SqlDbType.Int, 8))
comLath.Parameters("@.v_oq").Value = vol_oq.Text
comLath.Parameters.Add(New SqlParameter("@.i_oq", SqlDbType.Int, 8))
comLath.Parameters("@.i_oq").Value = oi_oq.Text
comLath.Parameters.Add(New SqlParameter("@.v_qn", SqlDbType.Int, 8))
comLath.Parameters("@.v_qn").Value = vol_qn.Text
comLath.Parameters.Add(New SqlParameter("@.v_qw", SqlDbType.Int, 8))
comLath.Parameters("@.v_qw").Value = vol_qw.Text
comLath.Parameters.Add(New SqlParameter("@.i_qn", SqlDbType.Int, 8))
comLath.Parameters("@.i_qn").Value = oi_qn.Text
comLath.Parameters.Add(New SqlParameter("@.i_qw", SqlDbType.Int, 8))
comLath.Parameters("@.i_qw").Value = oi_qw.Text

Try
comLath.ExecuteNonQuery()

Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try

comLath.Dispose()
ConLath.Close()




End If
End Sub

</script>

I'm not surprised if it fails when you leave the mandatory field empty. But I assume that's not what you meant, right?

The problem here relates to casting. Your empty text box returns an empty string. This would be fine for a varchar column, but if you try this with a column of type int, it will fail. You need to explicitly insert a null value in this case.

You could try something like this:

comLath.Parameters("@.v_oq").Value = (vol_oq.Text =="" ? DBNull.Value : vol_oq.Text);

|||

thanks for your prompt reply.

apparently the conditional operator ? works if u are using C#.

I am using the language vb, this implies i will have to use the if and then conditonal block for each, right?

|||

VB has the tertial operator IIF which is similar to ? operator, although it behaves somewhat differently (VB evaluates all parameters). 

comLath.Parameters("@.v_oq").Value = IIF(vol_oq.Text =="", DBNull.Value, vol_oq.Text)

|||

thanks so much it worked :)

however, the issue now is when i try to display the columns with null values, it reports an error -

i have explicitly casted these values with their corresponding data types to defualt value other than null. But the problem is like for eg, in case of any integer type it i set it to default of "0", for our business purpose its misleading as they would be expecting the sell of items for that day to be "0".

this is my code:

Public Function CheckDBNull(ByVal obj As Object, _
Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object
Dim objReturn As Object
objReturn = obj
If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then
objReturn = ""
ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then
objReturn = 0
ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then
objReturn = 0.0
End If
Return objReturn
End Function

|||

Then what do you want to display if an integer column is null? If you want to leave that field blank, simply return an empty string...

|||hi thanks for all your help... i have resolved the above query... much appreciated

Insert Proc With Both Select And Values

I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.

I created a test in MS Access and it loooks like this:

INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country

This works great in Access but not in SQL Server. In SQL Server 2 = @.PatientTripID

ANY SUGGESTIONS ON HOW TO HANDLE THIS?Hey, I tested your script. It works for me. Could you specify the error message and under what circumstance you are running this command and fail?|||Are you looking for something more like:CREATE PROCEDURE dbo.s2164
@.piPatientTripID INT
AS

INSERT INTO PatientTripRegionCountry_Temp (
CountryID, RegionID
, Country, PatientTripID)
SELECT Country.CountryID, Country.RegionID
, Country.Country, @.PatientTripID
FROM Country

RETURN-PatP|||This is my Stored Proc. It executes but the field PatientTripID is set to <Null>

CREATE PROCEDURE [dbo].[sp_PatientTripRegionCountryTemp_Insert_ForRegionID ]
@.RegionID int,
@.PatientTripID int,
@.PatientID int
AS
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, Country, RegionID, PatientTripID )
SELECT C.CountryID, C.Country, C.RegionID, @.PatientTripID
FROM Country C
WHERE (RegionID=@.RegionID)
GO

Any Suggestions?|||When you execute it from Query Analyzer, it should show "N row(s) affected" when it executes. Zero would be a bad thing in this case.

-PatP|||?? How are you calling the procedure? Can you give a couple examples?|||Thanks for all your help

Don't ask me why, but I retried the versions shown in #4 above and this time it worked.|||Way more gooder yet even! Glad you are back in business.

-PatP

Insert or update into a Image column causes data to double in size

We are experiencing problems inserting or updating image fields from
one table to another in SQL Server.

When we do this what ever size of file we insert is doubled in size
when it is inserted into the destination table.

This happens in insert and update queries, and if we use DTS.

Any help would be greatly appreciatedskyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are experiencing problems inserting or updating image fields from
> one table to another in SQL Server.
> When we do this what ever size of file we insert is doubled in size
> when it is inserted into the destination table.
> This happens in insert and update queries, and if we use DTS.
> Any help would be greatly appreciated

I'm afraid that it's difficult to help, since you don't disclose very
many details. Could you provide the code you use to copy the fields?

What is the typical size of the data you copy?

What you mean by "file ... is doubled in size"? What file? The database
file? But then you talk about inserting the file? How do you know
that it doubles in size?

Sorry for asking so many questions, but while you know very well what is
going on at your site, I know next to nothing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are storing documents such as PDFs, word documents etc in a object
field in a table. When we transfer the the contents from one table to
another using even a simple

INSERT INTO tblTest (ObjectField)
SELECT ObjectField
FROM tblTest2

The data in the destination is exactly doubled in size when you load
the document again ie when you save a 1Mb document into the table
transfer it to the second table it becomes 2Mb.

Hope this makes it more clear.|||skyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are storing documents such as PDFs, word documents etc in a object
> field in a table. When we transfer the the contents from one table to
> another using even a simple
> INSERT INTO tblTest (ObjectField)
> SELECT ObjectField
> FROM tblTest2
> The data in the destination is exactly doubled in size when you load
> the document again ie when you save a 1Mb document into the table
> transfer it to the second table it becomes 2Mb.

How do you deduce that?

It would help if you could provide a complete script that demonstrates
the problem. That would be a script that creates the table, populates
the first, and the loads the data into other, and then somehow gets
the size of the documents. I realise that including a 1MB object in
a script is not realistic, but the table pub..pub_info includes an
image column. Maybe you can build a repro script from that.

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

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