Inserting a new column in a merge replication

Hi,
I am beginner in replication
I use a merge replication with a pull subscription
I Use sql2000 Server 2000 for Publisher, Distributor and all Subscriber use
MSDE
I insert a new column in the temps tables
This table contain 161 thousands rows
I use this script to insert the new column
USE JMI
GO
sp_repladdcolumn @.source_object='temps',@.column='HeuresAutres',@.typ etext='
float NOT NULL DEFAULT 0 WITH VALUES ' ,@.publication_to_add='JMI_articles'
GO
After inserting the row i received this message
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'JMI_articles' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal'.
The stored procedure will still be created.
DO I have to worry about this message
I look in the tables and the new column was created with no errors
Also after I start a synchronisation with a subscriber to test if everything
was ok
Everything works ok but it took 45 minutes to insert the new column
"HeuresAutres" on the subcriber
Is it normal that it took so long
Is there a way I can optimise this because i find it to long
Thanks in advance
Check to see if sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal exists on the
publisher and subscriber. 45 minutes could be ok for a large table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:18BC357F-18CE-4674-AE20-6B3A1CD169B0@.microsoft.com...
> Hi,
> I am beginner in replication
> I use a merge replication with a pull subscription
> I Use sql2000 Server 2000 for Publisher, Distributor and all Subscriber
> use
> MSDE
> I insert a new column in the temps tables
> This table contain 161 thousands rows
> I use this script to insert the new column
> USE JMI
> GO
> sp_repladdcolumn @.source_object='temps',@.column='HeuresAutres',@.typ etext='
> float NOT NULL DEFAULT 0 WITH VALUES '
> ,@.publication_to_add='JMI_articles'
> GO
> After inserting the row i received this message
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'JMI_articles' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
> 'sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal'.
> The stored procedure will still be created.
>
> DO I have to worry about this message
> I look in the tables and the new column was created with no errors
> Also after I start a synchronisation with a subscriber to test if
> everything
> was ok
> Everything works ok but it took 45 minutes to insert the new column
> "HeuresAutres" on the subcriber
> Is it normal that it took so long
> Is there a way I can optimise this because i find it to long
>
> Thanks in advance
>
>
>
>
|||Yes they are on the subscriber and on the Publisher.
On the subscriber ther are a lot of store proc with a name looking like a
GUID
but all of them dont finish with _pal except this one
sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal
On the publisher they all finish by _pal
Is that normal ?
"Hilary Cotter" wrote:

> Check to see if sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal exists on the
> publisher and subscriber. 45 minutes could be ok for a large table.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "GC" <GC@.discussions.microsoft.com> wrote in message
> news:18BC357F-18CE-4674-AE20-6B3A1CD169B0@.microsoft.com...
>
>

Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Inserting a column in an existing table

I have an existing table (see below).

----
[FormCode] [varchar] (4) NULL ,
[FiscalYear] [char] (4) NULL
----

I want to add the column below after the [FormCode] when my SPROC runs.
----
[FiscalMonth] [char] (2) NULL
----

Any ideas would be a big help?
TIF--use this to add the column

alter table MyTable
add FiscalMonth char (2)
go

--and this to drop the column

alter table MyTable
drop column FiscalMonth
go

Cheers|||Thanks for your response, however, I'm actually after adding a column in between existing columns.

So in this case, my new column FISCALMONTH will be added between FORMCODE and FISCALYEAR.

Tnx|||Why is important to have the ordinal position of your column correct?|||The quickest and easiest (at least in most cases) way to "insert" columns into a table is to put the columns wherever they fall and construct a view to order them the way you want them.

In relational algebra, columns have no order. In relational databases, the order of columns should be considered an anomoly, not an attribute.

A view on the other hand is a template for a result set, and columns do have an order in a result set.

-PatP|||I don't understand eather... Why do you need them in a specific order?|||Did you ever get an answer to this? I know that you can create a view to order your columns but it would be nice to do this in the table. No it doesn't matter from a DB perspective but it is cleaner if you are dealing with many columns.|||Why don't you go into design view of a table in Enterprise Manager, make your changes, and save the script.

I would also summarize that ALTER TABLE anything in SQL server produces ineffeciencies at the page level...

Read Nigel's great article on the subject

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

Inserting a blank line betwen groupings in my matrix report

I have a matrix report with 3 column groups. My main group is called
"Location" and then I dump out a bunch of data related to that location.
I want to insert a blank line before each new "Location" in my report but I
can't figure out how to do this with my matrix report.
Any ideas?Try putting this into expression for the location:
=(Fields!Location.Value+Environment.newline())
Good luck!
Peace,
Dan
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:6B279444-7502-43B3-B238-9C282A3C8858@.microsoft.com...
>I have a matrix report with 3 column groups. My main group is called
> "Location" and then I dump out a bunch of data related to that location.
> I want to insert a blank line before each new "Location" in my report but
> I
> can't figure out how to do this with my matrix report.
> Any ideas?

Inserting a 0

Hi,
Im having trouble with the money data type, for instance I have a column that calculates a price but it will output the price as 470.2 instead of 470.20 which is how I want it displayed on a web page.

Anyone know how to automatically insert a zero on the end of the price?

THanksNoone knows how to insert zeros on the end of numbers??|||I'm gettin '470.2000'
from this simple query I ran from query analyser

declare @.dollar as money
set @.dollar=470.2
select @.dollar

I can't understand why your only getting 470.2. Maybe you can write your calculation query for us to figure?|||I figured it out but thanks anyhow : )sql

inserting 100 records

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

You mean like:

INSERT INTO t1(c1,c2)

SELECT '1','2'

UNION

SELECT '3','4'

UNION

...

?

|||

This will turn off the identity column for a table,

set identity_insert <tablename> on

[insert 100 records .. ]

set identity_insert <tablename> off

|||

No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example

Table1

ID StudRollNo StudName

Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like

ID StudRollNo Studname

|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005

Inserting .doc data into varbinary column

I need to put .doc data into a varbinary column for full text searching. I have created the db and columns but am unsure as to how to insert the varbinary data. I have found some discussions about inserting images but nothing explicitly on .doc files. Can anyone suggest resources or sample code?

The varbinary datatype does not differentiate the contents of the field, it's all just binary data as far as SQL is concerned. The samples you've found for images should apply equally to any type of binary object, it just seems that most examples are focues on image since most people want to store image data.

Mike

|||Thanks Mike. I will try those examples.

Inserted Identities

Hi i have a Query Like this:

INSERT INTO TABLE1
SELECT * FROM TABLE2

TABLE1 has a identity column,
now i want to know what identities have been inserted into TABLE1 after the Query executes.

Be Sure,
Hosseinhi try this

INSERT
INTO Table1
SELECT *
FROM Table2

-- assuming Col1 and Col2 are your unique column identifiersa
SELECT t1.TheIdentityColumn
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Col1 = t2.Col1
t1.Col2 = t2.Col2|||

You can do with @.@.ROWCOUNT.

Code Snippet

SET NOCOUNT ON;

Insert Into <Your Identity Table>

Select <some columns> from <some table>;

Select * From <Your Identity Table>Where identity_column > Scope_Identity() - @.@.Rowcount

|||

This use of SCOPE_IDENTITY() is not guaranteed to to work. It is possible and happens that rows can be inserted into the table in the middle of the sequence. If you are using SQL Server 2005, you can use the OUTPUT clause with your INSERT statement to fetch the identity columns of the inserted rows.

Rhamille's code will work if you have the alternate keys to the table.

|||I agree with Kent point.|||Here is how you can use the OUTPUT clause:

DECLARE @.table1 TABLE
(
IDCol INT
)

INSERT INTO Table1(fldlist)
OUTPUT INSERTED.IDCol INTO @.table1(IDCol)
SELECT * FROM Table2

SELECT * FROM @.table1 will give you the identity columns that were inserted.

Insert with condition

Hi,
what i am trying to do is, reading from an array which has the column name and value and then insert that value to the column which is fetched from that array.And on the other hand i should use select to get the data's which have the same ID.
more detail: what i have >> (VARID,columnName,Value)
what i want>> insert to table (columnName) values (Value) while ID =VARID

any idea how i should do that?
tnx
you can resort to dynamic SQL, e.g.

EXEC('insert into table (' + @.columnName + ') VALUES (' + @.value + ') WHERE ID =' + @.id)

HTH,|||Thanks for your reply. But that was my mistake that i thought Insert is what i need. I should use Update instead.Anyway it is the same story. I am using that in an c# application(web service).
I have this in my code:
objConnect = new SqlConnection(connectionString);
string queryString = "upadte MyTable set "+col+"= "+data+" where DeviceID = "+id+";";
try
{
objConnect.Open();
SqlCommand objCommand = new SqlCommand(queryString, objConnect);
objCommand.ExecuteReader();
}
...

It doesn't work, and because it is inside a web method i get the SoapException ..
I also tried with stored procedure,like this:
ALTER PROCEDURE [dbo].[UpdateData]
@.devID varchar(30),
@.dataCol varchar(30),
@.dataVal varchar(30)
AS
BEGIN
SET NOCOUNT ON;

update DataTransmission
set @.dataCol= @.dataVal
where DeviceID = @.devID
END
and it also doen't work when i do : exec UpdateData 'id','user','me'
Do you know what is going wrong?
|||

I am not sure about the cause of failure in your C# application, but the proc UpdateData will not give you desired result because " set @.dataCol= @.dataVal" will set the variable @.dataCol to have the value of the @.dataVal. The column is not touched at all. You can use dynamic sql inside the proc to achieve what you desire.

Thanks

|||I tried to use dynamic sql in stored procedure like this:
create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end
set col2 = case @.col when 'col2' then @.value end
where id = @.id

End

do you think it is right way?
|||that's just fine... but typing will be tedious if you have many columns in your table. |||But sql server has another idea it says there is syntax error (near '=').do you know what is wrong with it? when i check CASE in books, the syntax is right..So i don't know the problem.
|||Remove your second SET statement

create PROCEDURE [dbo].[UpdateTable1]
@.id varchar(30),
@.col varchar(30),
@.value varchar(30)
AS
BEGIN

update table1
set col1 = case @.col when 'col1' then @.value end,
col2 = case @.col when 'col2' then @.value end
where id = @.id

End

HTH,|||Thanks alot. finally works
|||no prob... glad to be of help

Insert Value list doest not match column list

HI...

I need to do a simple task but it's difficult to a newbie on ssis..

i have two tables...

first one has an identity column and the second has fk to the first...

to each dataset row i need to do an insert on the first table, get the @.@.Identity and insert it on the second table !!

i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"

here is the script

INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@.@.Identity,
?,
?,
?
)

what's the problem ?

Is that a cut and paste of your query?

There is a missing space between "Reference)" & "Values" in the first insert statement.|||Yes...it's a copy past....

I did what you ask and the problem remains the same|||Then you must not have all of the parameters mapped. Looks like 9 parameters.|||But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!|||

Alexandre Martins wrote:

But i'm sure that is a problem....because sql is not mapping automatic !! and i can't do it manual to !! it doesn't work!

When you click on the Column Mappings tab, you can't map the columns accordingly?|||

No! it's showing the warning "Insert Value list doest not match column list" and not mapping...

The funny thing is.....this way don't works

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID,CustomerID,AddressTypeID,TypeDescription)

VALUES(@.@.Identity,?,?,?)

but this way

INSERT INTO Address(CepID,Street,Number,Location,Complement,Reference)

Values(?,?,?,?,?,?)

INSERT INTO CustomerAddress(AddressID)

VALUES(@.@.Identity)

works perfect.....but i need the other fields....

i changed the table too to test....and with one field works....two or more "Insert Value list doest not match column list" and not mapping"

i don't know what to do....

Insert value in an auto-identity column

I'm a bit new to SQL Server, but here is the problem I am trying to get
around:

1. I have lets say an Employee table where the emp_id is an identity
column that is auto assigned by the db.
2. I wish to insert values into the tables, but sometimes I may wish to
use the value of the emp_id that i supply while inserting (and
sometimes not).

Is there any way in MS SQL to do this? For e.g. is it setup like a
constraint that I can temporarily drop. I do not want to alter the
table, so that option is ruled out.

Any other ways to do this (I know bcp is one of them that allows me to
do this, but looking for ways in the db itself using SQL if possible)

Thanks,
Bharat"Bharat" <go2bharat@.gmail.com> wrote in message
news:1112626803.244086.213160@.f14g2000cwb.googlegr oups.com...
> Xref: news.bluewin.ch comp.databases.ms-sqlserver:126921
> I'm a bit new to SQL Server, but here is the problem I am trying to get
> around:
> 1. I have lets say an Employee table where the emp_id is an identity
> column that is auto assigned by the db.
> 2. I wish to insert values into the tables, but sometimes I may wish to
> use the value of the emp_id that i supply while inserting (and
> sometimes not).
> Is there any way in MS SQL to do this? For e.g. is it setup like a
> constraint that I can temporarily drop. I do not want to alter the
> table, so that option is ruled out.
> Any other ways to do this (I know bcp is one of them that allows me to
> do this, but looking for ways in the db itself using SQL if possible)
> Thanks,
> Bharat

See SET IDENTITY_INSERT in Books Online.

Simon

insert value 1 into customer table column stand (was "Very Basic Sql")

Hello,

Bit of basic sql here for you:

I have a table called "customer" and there is a field in customer called stand.

Stand is also a seperate table that is joined to customer.

However i would like to add the value of "1" into STAND on the CUSTOMER table and i was wondering if someone could tell me the sql to do this. So basically where there is nothing insert a value of 1 into stand on customer.

Cheersinsert
into customer (stand)
values (1)|||how do i tell it to only insert that into blank values?|||ah, you're probably thinking of UPDATE, not INSERT
update customer
set stand = '1'
where stand = ' '|||Thanks, i will give that a try|||why has someone gone and changed the title of my post?|||Moderator changed unapropriate title ("Very basic sql") into something more meaningful ("insert value 1 into customer table column stand"). As you can see, he indicated the original thread name.

Read more about How to ask questions the smart way (http://catb.org/esr/faqs/smart-questions.html), especially "Use meaningful, specific subject header" chapter.|||Ok thanks for the information

Insert Uniqueidentifier after the fact

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of timeWhat sql script could I run to actually place a value for

Quote:

Originally Posted by

the newly created column for each record?

You can use NEWID() to backfill existing data:

UPDATE dbo.MyTable
SET MyColumn = NEWID()

--
Hope this helps.

Dan Guzman
SQL Server MVP

<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

>I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time
>

|||Thats perfect. Thanks for the help.
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

What sql script could I run to actually place a value for
the newly created column for each record?

>
You can use NEWID() to backfill existing data:
>
UPDATE dbo.MyTable
SET MyColumn = NEWID()
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time

Insert unique rows in temp table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

I don't think what you are doing is valid because a local temp table scope is very limited, but if it valid it will be covered in the link below by one of the best minds in T-SQL. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Insert Trigger

I guess the first question is "Can you have two columns in two seperate
table share an Identity Column?" If there is a way, then I'll do that.
The other solution that I have came up with is to have another table
that generates the IDs and then insert it into the record on Insert. I
need to know how to update a record contained in the Inserted table. I
tried doing it directly but I keep getting the error that states you
cannot alter the Inserted or Deleted tables. Any help would be
appreciated.Hi Toppar,
I guess the first question is "Can you have two columns in two seperate
table share an Identity Column?"
-No you can=B4t. There is no sequence in SQL Server like in Oracle.
YOu have to reference the table in your update statement using the
primary keys to join the original on the inserted one:
UPDATE SomeTable
SET SomeColumn =3D SomeValue
FROM SomeTable S
Inner Join INSERTED I
On S.JoinedColumns =3D s.JoinedColumns
--AND other joined columns
HTH, jens Suessmeyer.|||create table #t1(id int identity(1,2), j int)
insert into #t1(j)
select 1
union all
select 2
union all
select 3
select * from #t1
go
-- the idenitites wont collide
create table #t2(id int identity(0,2), j int)
insert into #t2(j)
select 1
union all
select 2
union all
select 3
-- the idenitites wont collide
select #t1.*, '#t1' from #t1
union all
select #t2.*, '#t2' from #t2
id j
-- -- --
1 1 #t1
3 2 #t1
5 3 #t1
0 1 #t2
2 2 #t2
4 3 #t2
(6 row(s) affected)|||Hi Jens,
I did finally get my plan to finally work after a lot of pain. I
pretty much had to do it your way but I had to add a default value so
that the unique constraint of the primary key was satisfied. I'm still
kind of new to row level locking in SQL Server, but if it works similar
to that in Oracle, I think that what I did should work. If not, I have
written code in the form to handle it and then retry if two users try
to insert at the same time. Once again, thank you for the help.
Jon...

insert to two tables

I posted this question previously but am now getting another error and fixing it (months ago). I am trying insert The ID2 column (pk) from table 2 into ID2 column (fk) of table 1. What the T-SQL is trying to do is check to see if the record in table 2 already exists in then just pass the value to the insert query for table 1. If the record does not exist then insert data into table 2first then perform the table 1 insert.

This SP works fine if the user does not exist in table 2. However, if the user does exist in table2 then instead of passing the ID, a null value is passed.

Can anyone tell me why the first query does not work? If there is an easier way of doing this then I am all ears.

Thank you

DECLARE @.IdentityHolder int

BEGIN TRANSACTION
IF EXISTS (SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
BEGIN
(SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
END
ELSE
BEGIN
INSERT INTO [tbl2] ([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)
END
COMMIT

SET @.IdentityHolder = (ID2)
INSERT INTO [tbl1]([ID2], [event])
VALUES (@.ID2, @.event)Your column list has 5 cols and your value list has 4?|||nope, tbl2 column list has four - ([ID2], [FN], [LN], emailAdd]) and the values has four - (@.ID2, @.fName, @.lName, @.emailAdd)|||OK,

What;'s this then?

SET @.IdentityHolder = (ID2)|||Because your ID2 in your set statement is not correlated to ant table/column value

Try this

BEGIN TRANSACTION
INSERT INTO [tbl2]
([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)

INSERT INTO [tbl1]
([ID2], [event])
VALUES (@.IdentityHolder, @.event)

IF @.@.error = 0
COMMIT
ELSE
ROLLBACK


You begin the tran ... If the first insert fails, the row exists in tbl2, so you insert into tbl1. If the first insert succeeds, the row did not exist in tbl2, so you have performed both inserts without an extra call to the database. The final check of @.@.error looks for a failure to insert into tbl1. If that <> 0, the entire transaction is rolled back.

Advanced error handling is left as an excercise to the developer.|||how does @.IdentityHolder know to get the value of ID2? @.IdentityHolder is a variable and needs to be set right?|||Violation of PRIMARY KEY constraint 'PK_tbl2. Cannot insert duplicate key in object 'tbl2'.

Cannot insert the value NULL into column 'ID2', table 'tbl1; column does not allow nulls. INSERT fails.|||i know what the violation is, when using the query you gave me I am not searching to see if the user already exists in the db. The "cannot insert a null" is the prblem I am still having. I am still unable to pull the fk value for tbl1 from tble2. Any ideas on how to do this?|||ok ... post the schema for both tables and let's work this out. I assumed in my solution that you were providing an incoming value for @.IdentityHolder. My mistake (you know what happens when you assume).

If this is not the case and you are looking for an identity value upon a successful insert into tbl2, you will want to use the scope_identity() function.|||yeah, i did scope first but it is giving me some other number. For example, if the value is 102 then scope gives me 17.

tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd|||so id1 in tbl2 is not an identity column?

Then you must be passing in a value for id1 if the entry does not exist in tbl2|||With this schema
tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd



CREATE proc Two_Table_Insert @.ID2 = null, @.ID1, @.FN, @.LN, @.emailAdd, @.event = null
AS
IF @.ID2 is not null
BEGIN
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
BEGIN TRANSACTION
SET @.ID1 = ID1
FROM tbl2
WHERE ID2 = @.ID2
COMMIT
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (ID2, ID1, FN, LN, emailAdd)
VALUES (@.ID2, @.ID1, @.FN, @.LN, @.emailAdd)
-- optional check status of 1st insert ...
-- rollback if failed and exit or do insert into tbl1 if event is not null
-- check status of second insert ... rollback if failed and exit
COMMIT
END|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.

Also I did mess up the scema, it should look like this

tbl1
ID1(pk)
ID2(fk)
event

tbl2
ID2(pk)
FN
LN
emailAdd

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

the data will enter the db like this;
tbl2
102, john, doe, jdoe@.email.com,
tbl1
+1, 102, request description.

my orginal code work fine except I wasn't pulling the pk for tbl2 to fill tbl1.|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.


so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?|||so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?

I guess I don't know what the term "identity column" means. ID2 is the PK of tbl2.|||BOL is your friend:




IDENTITY (Property)

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Note The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.


This is why I asked for the schema of the tables. a column that has the IDENTITY property will script out sort of like this


CREATE TABLE tbl2 (
ID2 int not null IDENTITY,
FN varchar(32) null,
LN varchar(32) null,
emailAdd varchar(255) null )


so ... does ID2 in tbl2 have the IDENTITY property? How about ID1 in tbl1?

If ID2 is an identity column, you can capture the value when you insert a row by using the SCOPE_IDENTITY() function. If not, then we have to use another way to get the value of ID2 upon insert into tbl2.|||Sorry, I know that as the primary key, but yes both ID1 and ID2 are Identity columns. I tried using scope_Identity but it only grabs the insert value of ID2 and not the value of ID2if it exist.|||You have stated that tbl2.id2 is an identity column, but you also stated in a prior post to this thread that :

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

The user does not provide the value of an identity column for an insert where the column is a primary key, unless you set identity_insert on. They can for the table where the value would be the FK.

Nevertheless, I'm going to take the data as stated in the quote above and give you what would work.


CREATE proc Two_Table_Insert @.ID2, @.FN, @.LN, @.emailAdd, @.event
AS
DECLARE @.tbl2ID2 int, @.err int
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
INSERT INTO tbl1 (ID2, event)
values (@.id2, @.event)

SELECT @.err = @.@.ERROR
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (FN, LN, emailAdd)
VALUES (@.FN, @.LN, @.emailAdd)

SELECT @.err = @.@.ERROR
IF @.err = 0
BEGIN
select @.tbl2ID2 = scope_identity()
INSERT INTO tbl1 (ID2, event)
values (@.tbl2ID2, @.event)

SELECT @.err = @.@.ERROR
IF @.err = 0
COMMIT
ELSE
ROLLBACK
END
ELSE
ROLLBACK
END
RETURN @.err|||still not working, the scope_Identity is getting the value from the tbl1 not tbl2. Is there a way to get the value of strored procedure parameters. Also I was wrong, id2 is not an Identity column (it is the PK) because it is supplied by the user.|||I got it to work i just used the @.ID2 in the value of both tables and it worked. When I first set up the db both values were automatically generated by the db, but then i changed the scema and forgot i could do it this way. thanks|||glad to help. should you post again, besure to include all elements of the schema ... as you can see, it was important to arrive at a workable solution.|||no doubt, i can see clearly now the rain is gone. ;)

INSERT statement; only 1 column in table.. that too identity

need to write an insert statement to a table with only identity column
without using IDENTITY_INSERT option
create table t (id int identity(1,1) primary key)
RakeshRakesh
create table test(id int identity)
insert test default values
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh|||Thanx
"Uri Dimant" wrote:

> Rakesh
> create table test(id int identity)
> insert test default values
>
>
> "Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
> news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
>
>|||create table t (id int identity(1,1) primary key)
INSERT INTO t default values
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy O
One way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith
|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell
|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell
|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
--
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> >I have a table that has 3 columns that I need to split up the data in
> > each column and insert into another table as rows.
> > As well for each record inserted the Status is set to "O".
> > I am thinking I stored procedure would be the way to go? But trying to
> > determine the best way to go about this.
> > Below is an example of the table structures.
> > I have 2 options, delete all the data from table2 and do a bunch of
> > inserts, or perform updates?
> > I am assuming the first would be better but having trouble with the sql
> > statement.
> > Any ideas?
> >
> >
> > table1
> >
> > ID Name1 Name2 CITY
> > 500 John Jeff TO
> > 501 Sheila Rose TO
> > 502 Barb Jen TO
> > 503 Tom Jerry TO
> > 504 Alan Scott TO
> > 505 Steve John TO
> > 506 Pat Cathy TO
> >
> >
> >
> > table2
> >
> > ID Name Status
> > 500 John O
> > 500 Jeff O
> > 501 Sheila O
> > 501 Rose O
> > 502 Barb O
> > 502 Jen O
> > 503 Tom O
> > 503 Jerry O
> > 504 Alan O
> > 504 Scott O
> > 505 Steve O
> > 505 John O
> > 506 Pat O
> > 506 Cathy O
> >
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:
> > <pisquem@.hotmail.com> wrote in message
> > news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> > >I have a table that has 3 columns that I need to split up the data in
> > > each column and insert into another table as rows.
> > > As well for each record inserted the Status is set to "O".
> > > I am thinking I stored procedure would be the way to go? But trying to
> > > determine the best way to go about this.
> > > Below is an example of the table structures.
> > > I have 2 options, delete all the data from table2 and do a bunch of
> > > inserts, or perform updates?
> > > I am assuming the first would be better but having trouble with the sql
> > > statement.
> > > Any ideas?
> > >
> > >
> > > table1
> > >
> > > ID Name1 Name2 CITY
> > > 500 John Jeff TO
> > > 501 Sheila Rose TO
> > > 502 Barb Jen TO
> > > 503 Tom Jerry TO
> > > 504 Alan Scott TO
> > > 505 Steve John TO
> > > 506 Pat Cathy TO
> > >
> > >
> > >
> > > table2
> > >
> > > ID Name Status
> > > 500 John O
> > > 500 Jeff O
> > > 501 Sheila O
> > > 501 Rose O
> > > 502 Barb O
> > > 502 Jen O
> > > 503 Tom O
> > > 503 Jerry O
> > > 504 Alan O
> > > 504 Scott O
> > > 505 Steve O
> > > 505 John O
> > > 506 Pat O
> > > 506 Cathy O
> > >
> >
> > I might not be following, but this should do what you wish.
> >
> > INSERT table2 (ID, Name, Status)
> > SELECT ID, Name1, 'O'
> > FROM Table1
> > UNION ALL
> > SELECT ID, Name2, 'O'
> > FROM Table1
> >
> >
> > Rick Sawtell

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote: