inset table using sp

Hi,
I have a select statement from tb1 and tb2:
select field1 from tb1 where field1 not in (select field1 from tb2)
order by field1
I want to have the result inserted into tb3 using a SP.
tb3 has only one filed, same type. How to do this?
ThanksCheck out INSERT INTO in BOL.
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
You can put it in a stored procedure like this:
CREATE PROCEDURE dbo.usp_DoInsert
AS
INSERT INTO tb3
SELECT field1 FROM tb1 WHERE field1 NOT IN (SELECT field1 FROM tb2)
GO
BTW you might want to reconsider your naming conventions - this particular
setup is going to be very hard to understand and maintain.
"whph" <nospam@.nospam.com> wrote in message
news:mqo2719mcnvjerec932v8i49mof003ikk4@.
4ax.com...
> Hi,
> I have a select statement from tb1 and tb2:
> select field1 from tb1 where field1 not in (select field1 from tb2)
> order by field1
> I want to have the result inserted into tb3 using a SP.
> tb3 has only one filed, same type. How to do this?
> Thanks

inserting\update line numbering

I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.|||If this is a one-time operation, usually a temp table is created with an identity. In other cases you may need to add an identity column to the table.

This is a daily build operation

Using Identity though creates the numbering for all records in the table (sees it as one order (500 records, records numbered from 1 to 500?)

I am looking at the line numbering to reset back to 1 everytime there is a change in the order number field (inv_ref field)

this can't be done through identity?|||My guess is, that it would be possible using an identity column, but I wouldn't go for that if it needs to be reset on a daily basis.

It's probably me, but I'm still not quite clear on what you want, on the other hand, maybe I do but miss the point as to why you need a linenumber associated with the table contents.

One of these might work for you though:
- create a view that has a computed column (if the linenumber can be determined on other information from the table);
- create a trigger that does an update (guess this can be quite a burden);
- create an sp; do an update based on identity from a temp-table.|||line number id forms part of the primary key make up.

I have Invoice number, sales order number, and line id

I can't include product id instead of line id as in an invoice there may be a reference to the same product id i.e. at line 1 and 10.

I guess a messy way of going about it is to just use identity and leave the count go on the entire table just to satisfy the primary key requirements.

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. Mark
This is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>
|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!
|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. MarkThis is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>

Inserting Values Into Primary Key and Foreign Key Tables

I have two tables that I would like to insert values into at the same time
and would like help with the SQL statement to do so. One table (Member_Info)
has a PK and the other (Image_Info) a FK. The relationship the two tables
share is through the (E_Mail) column. Example structure:
Member_Info table columns:
First_Name
Last_Name
Birthday
E_Mail (PK)
Image_Info table columns:
E_Mail (FK)
Use
Name
Please Help! Thanks!Use INSERT TRIGGER in Member_Info table
"Willie Davis via webservertalk.com" wrote:

> I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table (Member_Inf
o)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!
>|||Due to not determining what table will be filled by your code and what table
has to be filled "automagicaly", the following just depends on my guesswork.
CREATE TRIGGER TrgIns Member_Info
FOR INSERT
AS
BEGIN
INSERT INTO (EMail, Name)
SELECT EMail, COALESCE(FirstName,'') + ' ' + COALESCE(LastName,'') FROM
Inserted
END
I dont know wheter you Name column will store the First and the Lastname or
just one of it, but you should consider an Update Trigger to update this
information if its changed in the Primary Key Table. (Guess you sure wont
need it, if this just stores the name of the Member, even this is already
stored in the PK Table)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Willie Davis via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:4183b2e7baef434899249fa71b414bf6@.SQ
webservertalk.com...
>I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table
> (Member_Info)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
same time M<<
SQL works with one table at a time. Put you code into a stored
procedure, do two inserts and depend on DRI actions to maintain
integrity.

Inserting values into multiple tables

Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:

>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

inserting symbols in field

Hi All,
I am trying to insert symbols in a field of a table.

I am using the datatype nvarchar for the field.
However when i tried to insert a statement with the symbol pi, it comes out as following:
(A) 100? cm2 (B) 140? cm2 (C) 200? cm2

All the pi symbol are converted to ?.

Can ne 1 tell me how i can store such strings in the field.

Thanks

You're not stating how you're inserting the strings. If you're inserting using a plain INSERT statement you must N-prefix the string literals when using nvarchar.

INSERT INTO thetable (thecolumn,anothercolumn) VALUES(N'whatever',N'however')

inserting SQL statement in a table

Hi,
I want to insert an SQL statement in an sql table, i think i need an escape
character for the single quotes in statement?
declare @.statement varchar(255)
select @.statement = "select name from employees where fname = 'john' and
lname like 'chr%'"
insert into sqltable (str_text) values (@.statement)
If i use double quotes or braces in the statement, then i get an error.
Thanks in advance.
MAQMAQ
It's a good idea to use stored procedure that accepts parameters
Also , I hope you are aware of SQL injections, so with stored procedure you
will be more safely.
CREATE PROC spMyProc
@.fname VARCHAR(20),
@.lname VARCHAR(20)
AS
SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
+'%'
GO
EXEC spMyProc 'John','Braun'
"MAQ" <maq@.nos.pam> wrote in message
news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to insert an SQL statement in an sql table, i think i need an
escape
> character for the single quotes in statement?
> declare @.statement varchar(255)
> select @.statement = "select name from employees where fname = 'john' and
> lname like 'chr%'"
> insert into sqltable (str_text) values (@.statement)
> If i use double quotes or braces in the statement, then i get an error.
> Thanks in advance.
>
> MAQ
>|||Hi,
Thanks for the answer. I know i can do it very easily through stored
procedure. But the problem is that i am customising an existing very large
database. and they have all the SQL statements in a table. I only need to
modify some of those statements. Though i can do it through enterprise
manager directly, but I have to create an sql script which updates those
statements in the table.
/MAQ
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJ86wKmRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure
> you
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>|||use double single quotes inside the string and single quotes for statement
itself:
select @.statement = 'select name from employees where fname = ''john'' and
lname like ''chr%'''
that will do the trick
Hope it helps
"Uri Dimant" wrote:

> MAQ
> It's a good idea to use stored procedure that accepts parameters
> Also , I hope you are aware of SQL injections, so with stored procedure yo
u
> will be more safely.
> CREATE PROC spMyProc
> @.fname VARCHAR(20),
> @.lname VARCHAR(20)
> AS
> SELECT <column lists> FROM Table WHERE fname =@.fname AND lname LIKE @.lname
> +'%'
> GO
> EXEC spMyProc 'John','Braun'
>
>
> "MAQ" <maq@.nos.pam> wrote in message
> news:%23RpXXGmRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> escape
>
>

Inserting Records, Skipping Duplicates

I'd like to ask if there's any statement to insert records into a table, suc
h
that if any record violates the primary key constraint, it will "neglect" th
e
record and insert the next one.
Thank youAn exception/error will be generated if you try to insert a row that
violates the primary key constraint. You can pre-empt the primary key
constraint violation by checking each row on insert via trigger.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:CC9F8302-1C4D-4141-B9B1-EADC999CC01B@.microsoft.com...
> I'd like to ask if there's any statement to insert records into a table,
> such
> that if any record violates the primary key constraint, it will "neglect"
> the
> record and insert the next one.
> Thank you|||Hi,
Maybe if you perform your inserts one row at a time within a loop you could
handle the errors with @.@.ERROR.
Ray
"wrytat" wrote:

> I'd like to ask if there's any statement to insert records into a table, s
uch
> that if any record violates the primary key constraint, it will "neglect"
the
> record and insert the next one.
> Thank you|||Add a WHERE NOT EXISTS() to the INSERT.
INSERT Whatever
VALUES('a', 'b', 'c')
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = 'a')
or
INSERT Whatever
SELECT A, B, C
FROM Somewhere
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = Somewhere.A)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 19:30:02 -0700, wrytat
<wrytat@.discussions.microsoft.com> wrote:

>I'd like to ask if there's any statement to insert records into a table, su
ch
>that if any record violates the primary key constraint, it will "neglect" t
he
>record and insert the next one.
>Thank you

Inserting picture into SQL Server Database through Table

How to insert a picture into SQL Server 2000 table?
I want
1)Table structure.
2)Insert statement.
3)One example.
Seethis post on this topic.

Inserting Null values

Hi,
I am trying to insert null values into sql server from my access from. I am using sql statement. But it says 'Syntex error in Insert statement'. When i remove null values it works fine? How can I insert null values into a table?
Any help will be highly appreciated.It would help if you post the statement...|||Use SQL Pass-Through Query:

INSERT INTO Table1 ( id, name ) values(13, null);|||If your table accepts null in the field (name) then you don't need to mention that in your isert statment. Just use:

INSERT INTO Table1 (id) values(13);