Insert Variables to a table.

Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.Variables have a scope of one batch. Thus, the variable evaporated before
it got to you INSERT statement.
BTW, the first batch would have done the SELECT, but the PRINT should have
given you NULL, since the variable was never assigned. Therefore, run the
following in its entirety:
declare @.RecNo as int
select @.RecNo = count(Recno) from Help
print @.Recno
insert into OtherFile (recno)
values (@.RecNo)
go
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
Hi all. New to MS SQL, so this is probably dumb.
I've looked at all kinds of examples on the web and just can't seem to get
it.
I am trying to insert into a column (in an SQL2K) table a value from a
variable.
Here is an example of what I'm coding in Query Analyzer:
<< Code Start>>
declare @.RecNo as int
select count(Recno) from Help as RecNO
print @.Recno
Code Ends>>
UP TO HERE, this works fine and actually prints the number 1012 (which are
the correct number of records in the table).
OtherFile is just a name I'm using as an example.
<< Code Starts
insert into OtherFile (recno)
values (@.RecNo)
Code End>>
When I execute this, I get the message "Server: Msg 137, Level 15, State 2,
Line 2
Must declare the variable '@.RecNo'.
I thought I had declared it as I can print it, but I can't insert it into
anything.
What the heck am I doing wrong?
Thanks to all.|||Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>|||There's difference in the output between a PRINT and a SELECT. Your
original script would have output one row with the correct number. If you
click on the Messages tab, that's where you see the output of any print
statements, assuming output in a grid. Oh, and I tried it and I got no
output in the Messages tab when the PRINT statement was printing a variable
that had not been assigned. I had thought it would have printed a NULL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
news:OM%23ChAOGIHA.3360@.TK2MSFTNGP04.phx.gbl...
Thanks Tom, that did it.
Also, I exited Query Analyzer, relaunched and ran the first part of my
script. It now returned 1013, so the insert worked.
I'm not sure why the Print displayed 1012 orignally. Maybe a ghost?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ODLfw6NGIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Variables have a scope of one batch. Thus, the variable evaporated before
> it got to you INSERT statement.
> BTW, the first batch would have done the SELECT, but the PRINT should have
> given you NULL, since the variable was never assigned. Therefore, run the
> following in its entirety:
> declare @.RecNo as int
> select @.RecNo = count(Recno) from Help
> print @.Recno
> insert into OtherFile (recno)
> values (@.RecNo)
> go
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Mathew Rizzal" <mrizzal@.yahoo.ca> wrote in message
> news:ufwyI3NGIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Hi all. New to MS SQL, so this is probably dumb.
> I've looked at all kinds of examples on the web and just can't seem to get
> it.
> I am trying to insert into a column (in an SQL2K) table a value from a
> variable.
> Here is an example of what I'm coding in Query Analyzer:
> << Code Start>>
> declare @.RecNo as int
> select count(Recno) from Help as RecNO
> print @.Recno
> Code Ends>>
> UP TO HERE, this works fine and actually prints the number 1012 (which are
> the correct number of records in the table).
> OtherFile is just a name I'm using as an example.
> << Code Starts
> insert into OtherFile (recno)
> values (@.RecNo)
> Code End>>
> When I execute this, I get the message "Server: Msg 137, Level 15, State
> 2,
> Line 2
> Must declare the variable '@.RecNo'.
> I thought I had declared it as I can print it, but I can't insert it into
> anything.
> What the heck am I doing wrong?
> Thanks to all.
>
>

Insert Trigger - How To

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

Maybe you can try CLR integration in SQL2005 Create a stored procedure from an assembly?which?generate?the?password,?and?then?call?the?stored?procedure?in?the?insert?trigger?on?the?table.?Take?a?look?at?this?article:Using CLR Integration in SQL Server 2005

But if you just want some random string, why not try?T-SQL new_id() funciton?sql

Insert Trigger

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

You can create a dll which contains a method to call System.Web.Security.Membership.GeneratePassword(12,4) and return the generated password. Then add the dll to SQL assemblies and create a CLR reference function for that assembly.?For?more?information?you?can?refer?to:
Using CLR Integration in SQL Server 2005

INSERT Statement Error

I have a web app w/a form that takes user data and inserts into a customers
table. Now if I try to insert a record w/identical 'customerName', this is
the error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_transactions_customers'. The conflict occurred in database 'CHZ', table
'customers', column 'CID'.
Now the constraint 'FK_transactions_customers' uses CID.Customers as my
Primary key and Trans.CID as the foreign key. I can't seem to figure out wh
y
the error is thrown when using an existing 'customerName'.
Here's the ddl:
CREATE TABLE [customers] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDB] [bit] NULL ,
CONSTRAINT [PK_customers] PRIMARY KEY NONCLUSTERED
(
[CID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
TIA for help.Post the DDL for Trans, as well as the offending INSERT statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:FB917852-FA0D-4EE8-B6DD-15A142D3B934@.microsoft.com...
I have a web app w/a form that takes user data and inserts into a customers
table. Now if I try to insert a record w/identical 'customerName', this is
the error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_transactions_customers'. The conflict occurred in database 'CHZ', table
'customers', column 'CID'.
Now the constraint 'FK_transactions_customers' uses CID.Customers as my
Primary key and Trans.CID as the foreign key. I can't seem to figure out
why
the error is thrown when using an existing 'customerName'.
Here's the ddl:
CREATE TABLE [customers] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDB] [bit] NULL ,
CONSTRAINT [PK_customers] PRIMARY KEY NONCLUSTERED
(
[CID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
TIA for help.|||You are trying to insert a customer which isn=B4t present in the
transaction table. Check this.
HTH, jens Suessmeyer.|||"Jens" wrote:

> You are trying to insert a customer which isn′t present in the
> transaction table. Check this.
> HTH, jens Suessmeyer.
>
Shouldn't be a problem, unless the foreign key constraint is backwards
(customers references transactions instead of transactions references
customers).

insert special characters in database

I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?Depends what you mean. If you mean unicode and you are attempting to insert into CHar or VarChar fields then you need to use NVarChar or NChar. BoL has all the gory details. Otherwise you'll need to porovide more details.

HTH|||it seems everytime I include an apostrophe the system gives me an error..not sure why this is happening

my second question is-- when someone types this:

I cant wait
to leave
this place

--It come out like this
I cant wait to leave this place

How can I make the system print all the tab keys and return carriage?

thank you for any responses|||You need to escape apostrophes.

"I can't wait to leave this place"
must be submitted as
"I can''t wait to leave this place"

HTH|||The latter problem is probably a presentation issue - your FE needs to interpret the carriage returns & line feeds.|||hi everybody,

This is Rajen, hope it will be nice sharing ideas with u all. Feeling nice to join u all .:beer:

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
======================
Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
================================================== ======================
INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
================================================== ========================
CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
Nate
I suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.c om...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================
> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ================================================== ======================
> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ================================================== ========================
> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
====================== Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
======================================================================== INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
========================================================================== CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
NateI suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.com...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ========================================================================> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ==========================================================================> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

Insert or Update Row if Primary key isn't pre-existing in table SQL 2000

I'm trying to extract data from our Accounting Database and use it in
another database that's used for our web site. Last month I created a SQL
Select Query to give me the product information I need to put into our web
site database. I then used DTS to copy that data from one database to
another. Now I need to update my web site database with any new products
that have been added into our Accounting database without changing any
existing rows in the web site database. Basically, I just need to be able
to add a new row for a product if the product ID (primary key) isn't
anywhere in my web site database. Here's my original query to retrieve
product info from the Accounting database:
SELECT DISTINCT IV00101.ITEMNMBR AS ID, IV00101.ITEMDESC AS Title,
IV00101.USCATVLS_2 AS Category
FROM IV00101 LEFT JOIN AARG_Inv_UserDef_Item ON IV00101.ITEMNMBR =
AARG_Inv_UserDef_Item.ITEMNMBR
WHERE ((IV00101.ITEMTYPE)=1 AND ((IV00101.USCATVLS_2)<>'box' And
(IV00101.USCATVLS_2)<>'replicator'
And (IV00101.USCATVLS_2)<>'components' And (IV00101.USCATVLS_2)<>'displays'
And (IV00101.USCATVLS_2)<>'Dist Audio'
And (IV00101.USCATVLS_2)<>'Dist Video' And (IV00101.USCATVLS_2)<>'Dist
Games' And (IV00101.USCATVLS_2)<>'Dist Softw'
And (IV00101.USCATVLS_2)<>'Dist Books' ))
ORDER BY IV00101.ITEMNMBR;
RESULTS
40139 James Earl Jones reads the Bible (CD/Small)
Devotional
40151 In Their Own Words: Space Race (CD/Small) Spoken
Word
40155 Old West Collection (CD/Small)
Spoken Word
40159 Lewis & Clark Collection (CD/Small)
Spoken Word
40162 Ingles (CD/Large)
Lang LearnColin wrote:
> I'm trying to extract data from our Accounting Database and use it in
> another database that's used for our web site. Last month I created
> a SQL Select Query to give me the product information I need to put
> into our web site database. I then used DTS to copy that data from
> one database to another. Now I need to update my web site database
> with any new products that have been added into our Accounting
> database without changing any existing rows in the web site database.
> Basically, I just need to be able to add a new row for a product if
> the product ID (primary key) isn't anywhere in my web site database.
> Here's my original query to retrieve product info from the Accounting
> database: SELECT DISTINCT IV00101.ITEMNMBR AS ID, IV00101.ITEMDESC AS
> Title,
> IV00101.USCATVLS_2 AS Category
> FROM IV00101 LEFT JOIN AARG_Inv_UserDef_Item ON IV00101.ITEMNMBR =
> AARG_Inv_UserDef_Item.ITEMNMBR
> WHERE ((IV00101.ITEMTYPE)=1 AND ((IV00101.USCATVLS_2)<>'box' And
> (IV00101.USCATVLS_2)<>'replicator'
> And (IV00101.USCATVLS_2)<>'components' And
> (IV00101.USCATVLS_2)<>'displays' And (IV00101.USCATVLS_2)<>'Dist
> Audio'
> And (IV00101.USCATVLS_2)<>'Dist Video' And (IV00101.USCATVLS_2)<>'Dist
> Games' And (IV00101.USCATVLS_2)<>'Dist Softw'
> And (IV00101.USCATVLS_2)<>'Dist Books' ))
> ORDER BY IV00101.ITEMNMBR;
> RESULTS
> 40139 James Earl Jones reads the Bible (CD/Small)
> Devotional
> 40151 In Their Own Words: Space Race (CD/Small)
> Spoken Word
> 40155 Old West Collection (CD/Small)
> Spoken Word
> 40159 Lewis & Clark Collection (CD/Small)
> Spoken Word
> 40162 Ingles (CD/Large)
> Lang Learn
Have a look at NOT EXISTS to insert only rows that do not exist. ORDER
BY clauses will just add unnecessary overhead unless you are inserting
in the destination table's clustered index order.
SELECT
COL1,
COL2
FROM
dbo.SOURCE_TABLE
WHERE
COL3 = 5
AND NOT EXISTS (
SELECT * FROM dbo.DESTINATION_TABLE WHERE DESTINATION_TABLE.COL4 =
SOURCE_TABLE.COL4)
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thank you! Both of your recommendations have helped me out. Here's my
final query
I ended up using the Not Exists query
AND NOT EXISTS (SELECT * FROM TopicsWeb.dbo.tblProduct WHERE
TopicsWeb.dbo.tblProduct.Product_ID = IV00101.ITEMNMBR);
Put the above into a Insert INTO statement and now I can synch the two DB's
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uX4PPvC$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Colin wrote:
> Have a look at NOT EXISTS to insert only rows that do not exist. ORDER BY
> clauses will just add unnecessary overhead unless you are inserting in the
> destination table's clustered index order.
> SELECT
> COL1,
> COL2
> FROM
> dbo.SOURCE_TABLE
> WHERE
> COL3 = 5
> AND NOT EXISTS (
> SELECT * FROM dbo.DESTINATION_TABLE WHERE DESTINATION_TABLE.COL4 =
> SOURCE_TABLE.COL4)
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com