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

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, SCOPE_IDENTITY, and parameters...

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening.

 SqlConnection conn =new SqlConnection(@."Server=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open();try { SqlCommand cmd =new SqlCommand(); cmd.Connection = conn;// delete all rows cmd.CommandText ="DELETE FROM Person"; cmd.ExecuteNonQuery(); Response.Write("start... <br><br>");// ad-hoc insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)"; cmd.ExecuteNonQuery(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();// parameter insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@.PersonID, @.PersonName, @.PersonAge)"; p =new SqlParameter("@.PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonName","Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery(); Response.Write("<br>end."); }finally { conn.Close(); }

I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...

Thank you.

If I add "; SELECT SCOPE_IDENTITY()" to the INSERT statement and use ExecuteScalar instead of ExecuteNonQuery I get the identity back. The question is, why do I have to do this? If I use the SQL Server Profiler I don't see any difference between the two methods. Any thoughts?

Thank you.

|||Your SCOPE_IDENTITY() should be in the same batch as your INSERT. You are making 3 separate calls each with a different T-SQL statement and each one is treated independent of other. Better yet, use a stored proc and do it all in one place.|||

What exactly defines a batch? And if CommandText = "INSET INTO Table (fields..) VALUES (values); SELECT SCOPE_IDENTITY()" is a single batch when using parameters, why does SCOPE_IDENTITY work in two separate "batches" when not using parameters? I guess, what I'm trying to figure out is why are how are they different? Thanks.

|||

Hi,

A batch is guaranteed to be executed together. If you execute the command in 2 batches, there is possibility that a concurrent user may be also inserting into that table. Then your SELECT SCOPE_IDNETITY() query might return the incorrect number.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

sql

Insert with SELECT@@IDENTITY (sqljdbc 1.2)

Hi!

We are porting a database from DB2 to SQL-Server 2005. We use WAS 5.1. We have problems with insert staments followed by an SELECT @.@.IDENTITY. The sql error is that no result set is returned. The syntax is "<insert statment>;\nSELECT @.@.IDENTITY.
We have no problems with this in the 1.1 version (there we have the known "read date" error).

Joachim

Joachim,

Thank-you for notifying us of this issue. We are investigating this problem.

Jimmy

|||

Hello Joachim,

Can you provide us with a standalone java repro. I'm interested in learning which of the Statement's execute method you are using and how you are using it. I tried to author a quick repro in house but was unsuccessful.

boolean returnValue = stmt.execute("insert into GenKeys values('text')", Statement.RETURN_GENERATED_KEYS);

System.out.println("Returned gen keys: " + returnValue);

rs = stmt.getGeneratedKeys();

while(rs.next())

{

System.out.println("Got back Generated Key = " + rs.getInt(1));

}

BTW: I am also curious to learn the intent of using select @.@.identity. JDBC provides functionality for accessing generated keys via the Statement.getGeneratedKeys() method.

Thanks,

Jaaved

|||

Hi!

We used a PreparedStatment with executeQuery(). It worked well with 1.21. The getGeneratedKeys() method is not supported by websphere 5.1 (wich is an IBM problem...). In 6.0 i think this is fixed. Until then we did find an other solution to our problem:

Code Snippet

public long getGeneratedKey(PreparedStatement ps) throws SQLException {
ResultSet rs = null;
try {
if (ps.getMoreResults()) {
rs = ps.getResultSet();
}
if (rs != null && rs.next()) {
return rs.getLong(1);
}
return -1;
} catch (SQLException e) {
try {
rs.close();
} catch (Exception ignore){}
throw e;
}
}

It′s not perfect but it seems to work (in cases with only one insert followed by an SELECT @.@. IDENTITY). Is the trouble with executeQuery() a bug or is that how it should be?

Joachim

Insert with a Identity

Say you have a temp table with three columns (f1,f2,f3)
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?
If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:

> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>
|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve

Insert with a Identity

Say you have a temp table with three columns (f1,f2,f3)
and you want to insert them into a permanent table that
has four columns (f1,f2,f3,f4) where f4 is an identity
column. Assuming you don't want to DTS, what is the
correct syntax to do this? I have tried things like
insert into permtable
select * from #temptable
values (f1, f2, f3)
but of course it fails. Any ideas?If you're not sensitive about what value gets placed in the identity column,
then all you need to do is qualify your destination columns in the insert.
That, and you don't want the "values..." line in your insert...select.
insert into permtable (colA, colB, colC)
select f1, f2, f3 from #temptable
"HTX" wrote:
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
>|||"HTX" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c4a589$d2355670$a401280a@.phx.gbl...
> Say you have a temp table with three columns (f1,f2,f3)
> and you want to insert them into a permanent table that
> has four columns (f1,f2,f3,f4) where f4 is an identity
> column. Assuming you don't want to DTS, what is the
> correct syntax to do this? I have tried things like
> insert into permtable
> select * from #temptable
> values (f1, f2, f3)
> but of course it fails. Any ideas?
Have you tried:
insert into permtable (f1, f2, f3)
select * from #temptable
Steve

insert values into both table at the same time using sql server 2005

hi all,

In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.

table 1:

tid--> int,identity,primary key

tname-->varchar(200)

table 2:

sid-->int,identity,primary key

tid-->fk (this tid is set as foreign key for the tid in table1)

now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..

pls help me..

thanks

swapna

Go For Stored Procedure for Insert...

SP Flow should be-

1. Start SQL Transaction

2. Insert into Table1

3. Get the Inserted INDENTITY value.

4.Insert into table 2

5. Commit Transaction Or Rollback transaction depending on the Error .

|||

You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.

For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.

Feel free to ask for more help on this issue.

|||

Or you can create a trigger to insert the row into the second table.

It if must always happen the same way, the trigger would be a safer bet.

check out create trigger in the documentation.

sql

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 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 thru a view to a table with an IDENTITY property

Why doesn't my identity property function normally when I try to insert
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:

>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>

>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.

>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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 SQL statament ?

I have a two table and they have relationship :

tblOrder :
+ OrderId : Type is AutoNumber(Identity) (PK)
+ CustomerID : Number(int)
+ OrderPrice : Number(int)
+ OrderDate : DateTime

and tblOrderDetail :
+ OrderID : Type is Number(FK)
+ ItemID : Number(int)
+ PriceEachItem : Number(int)

My problem : I want to insert data into two above table .
To tblOrder I use : insert into tblOrder(CustomerId,OrderPrice,OrderDate) values(.....) <--(This statement is allright )

So how i insert into tblOrderDetail when i can't not identify OrderID.

Please help me ! Thank u very much !

Insert into the order table, then use the SCOPE_IDENTITY() function to get back the key for that row...
DECLARE @.OrderID INT
INSERT tblOrder VALUES (...)
SET @.OrderID = SCOPE_IDENTITY()
INSERT tblOrderDetail VALUES (@.OrderId, ...)
|||

Thank adammachnic very much ! i am a beginner ! because i can only know "simple sql statement " i don't know clearly your sql statement ! How can i use them in Asp.net . Example in the my situation :

Sub Order_Click(a as Object ,e as EventArg) handles button1.Click
dim myconn as new sqlconnection("...")
dim sql as string ="Insert into tblOrder(...) values(...)"
dim mycmd as new sqlcommand(sql,myconn)
mycmd.executenonquery !
End Sub

I have never forgotten your advice ! Thank !

|||Actually, it would be better to put these statements into storedprocedures instead of calling them directly from your app. Youshould probably invest in a good book on SQL Server and ADO.NET. There are a few books available that cover both topics, that shouldhelp you out quite a bit!

|||

Thank adam ! I am going to study like you guide !

Insert row in table with Identity field, and get new Identity back

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?

TIA,

barkingdog

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

If you need to do this for every row, then using identities with an SSIS is not a good idea. You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS. Even then, you only get back the last identity - which may not be what you expect if a parallel process has added a row between you commiting your row and asking for it's identity.

The best way is to use a script to generate a key in the data flow. In that way, you will know what the key value for each row is in advance and it can be inserted (thanks to multicast) into different tables at once, guaranteeing referential integrity.

Donald

|||

Donald,

When you wrote "You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS."

When I run a normal SSIS package that reads from a file and writse to a database isn't one row being committed at a time? Or does SSIS save as many rows as possible in, say a memory buffer, and then commit then all at once?

TIA,

barkindog

|||

Strictly speaking it is the provider that handles commits, not SSIS.

The Fastload option on the OLEDB provider allows you to set batch sizes from 1 to "the entire data load in one batch."

If you do not use Fast Load, then one row at a time is sent.

The OLEDB command component also processes one row at a time.

However, in all these cases, the problem is not the performance of handling one row at a time (although that is a real factor) - it is also that you cannot get back the identity for the row you have just committed.

The pattern in SQL Server (and in most rdbms's) is that you can get the last identity issued. It is tempting to think that having just posted a row, the last identity issued must be for that row. Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization.

I much prefer issuing keys in advance in the ETL process - you can do so much with them, with great performance and guaranteed integrity.

Donald

|||

Regarding "Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization."

1. If my job is the only one updating the table with the Identity column , and I'm not running multiple copies of my job, then I presume that parallellism can't happen to me. Or does SSIS do things "in the background" that could cause a smidgin of parallelism, even for my particular case?

2. Later on I will need to re-run my job with new data. Then I have to read the current value of the Identity from the table, add 1 to it, and begin with that value. Your argument about parallelism makes me wonder if the only way to accurately read the identity value from a table is to make sure no other app updates that table. (That sure puts a dent in the possibility of scaling out horizontally with servers.)

TIA,

barkingdog

|||

1. The OLEDB command destination may send a command for the second row before the first has completed. Our buffer architecture is designed to maximise the potential for pipeline parallelism.

2. The only way to guarantee that the last identity you read is the last one you inserted, is to be able to guarantee that no process has written to the table since your process.

We do have a design pattern for highly parallel key generation that may (but may not) be in the next version . Either way there will be a paper on this at some point.

The best strategy is to know your keys in advance - by generating them in your data integration process. That way, you have complete control.

Donald

INSERT Record through a view

Is is possible to insert a record through a view. If so, how?

USE Northwind

GO

CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)

GO

ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO

CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)

GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)

GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms

GO

-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO

SELECT * FROM MyProducts
GO

DROP VIEW MyProducts
GO

DROP TABLE tbFlatPanelForms
GO

DROP TABLE tbDoubleTeeForms
GO

DROP TABLE tbForms
GO

Mike Bithink it's imposible to do with view with union
in your case you woudl like to insert data into 3 tables

maybe tray insert data into 2 tables and triger to put data into 3-th table|||you can insert into a view but you can only affect one table.
so in the case of unions this is not possible
BUT......
you can however use an instead of trigger to check for which table the insert is going to and then instead of inserting through the view, you insert directly to the correct table.|||BOL:

Updatable Partitioned Views
If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view if:

The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.|||rdjabarov
no partition mentioned. so went with the conservative option

hey
how about some liquor this friday?|||Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know.|||Originally posted by rdjabarov
Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know. Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP|||Hell...he'll be still backing out of his driveway by the time I get across NJ

:D

and a shamless 2500th post...

And you should look into partitioned views...the contraints have to be very specific...

but updating the base table is the best performing method...

for the view, the optimizer will still look at a tables in the view...|||Originally posted by Brett Kaiser
and a shamless 2500th post... Uff da! That's a lot of postings! Congratulations.

I still think you should have posted #2500 into the Yak Corral!

-PatP|||Originally posted by Pat Phelan
Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP

Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!|||Yeah, I had a car like that once...

;)

-PatP|||northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues|||Originally posted by GDMI
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!
Over the christmas holidays I drove a plymoth breeze from Windsor Ontario Canada -> South Padre Island, Texas -> Orlando Florida -> Back to Windsor

Just under 7000 kilometers if I remember correctly.

Great time though!

Mike B|||Originally posted by Ruprect
northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues The best I can come up with is "Well duh!"

Why put the exits closer together? Who would want to get off? If you think that the highway inhales vigorously, you should have tried getting off the highway somewhere in northern Florida!

-PatP

INSERT Query, Guid AutoIncrement Help

Im still learning my way around SQL and queries and i was wondering :

How do you get a SQL Table to autoincrement a Guid? (is it "Is Identity?" or "RowGuid"...)

How would i create a new row with a new Guid, and insert into the values i want without specifying the Guid?

You would need to use NewID() to get the next random GUID.

INSERT INTO yourTable (col1, col2,...) VALUES (@.val1, NewID(), @.val3,...)

Insert Query on table with identity column

I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.

tbl_appointment
------
1. appt_id (pk) -- identity column, seed 25, increment 1
2. date_id
3. time_start
4. time_end
5. appt_details
6. lkp_emp_id

Private Sub btnAddAppts_Click()
On Error GoTo Err_btnAddAppts_Click
Dim strsql As String
DoCmd.SetWarnings False
strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True
DoCmd.Close

Exit_btnAddAppts_Click:
Exit Sub

Err_btnAddAppts_Click:
MsgBox Err.Description
Resume Exit_btnAddAppts_Click
End Sub

I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'.

It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."[posted and mailed, please reply in news]

Dave (dcermelixx@.ucwphilly.rr.com) writes:
> I cannot insert into my appointments table because the primary key and
> identity column, appt_id, cannot be added. What do I have to change in
> my SQL statement to add new records into this table? I'm using SQL
> Server 2000 BE with Access Data Project FE.
> tbl_appointment
> ------
> 1. appt_id (pk) -- identity column, seed 25, increment 1
> 2. date_id
> 3. time_start
> 4. time_end
> 5. appt_details
> 6. lkp_emp_id
>
> Private Sub btnAddAppts_Click()
> On Error GoTo Err_btnAddAppts_Click
> Dim strsql As String
> DoCmd.SetWarnings False
> strsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start,
> time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID &
> "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails &
> "')"
> DoCmd.RunSQL strsql
> DoCmd.SetWarnings True
> DoCmd.Close

This is not a good way of writing SQL statements. Try to enter
the value "It's good" in txtApptDetails to see what happens.

The above is open for an attack known as SQL injection, whereby an
attacker can change your SQL statement to do something you did not intend.

The remedy is to add parameterized statments:

INSERT [tbl_appointments)
(lkp_emp_id, date_id, time_start, time_end, appt_details)
VALUES (?, ?, ?, ?, ?)

The client library then takes care of necessary quoting, converting of
date formats etc. (The above presumes that SQL Server will interpret
the dates, which may not work well.)

I don't really know which client library you are using, so I can't tell
how you would do it. But should definitely investigate the possibilities.

> I did check through Access and through Enterprise Manager and it is
> setup correctly. So I returned all rows in enterprise manager to
> manually enter an appointment to the table. I get the same error when
> doing data-entry straight to the table.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity
> column 'appt_id'.

Since the INSERT statement looks OK, I would look into whether there is
a trigger on the table.

If you run the INSERT statement from Query Analyzer, do you get the
same error message? In such case, pay attention on whether the error
message includes a procedure name.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> INSERT [tbl_appointments)
> (lkp_emp_id, date_id, time_start, time_end, appt_details)
> VALUES (?, ?, ?, ?, ?)

I agree, and I used to think this was silly.

What you need to do is set this up as a command object, and then add
five parameter objects to it. Right before you fire the command, you
plug in their values. Example:

Dim cmd as new oledb.oledbCommand("Insert...", dbConn)
cmd.Parameters.Add( New Paremeter("@.lkp_emp_id", dbVarChar)

Then

cmd.Parameters(0).Value = 7
cmd.ExecuteNonQuery

> If you run the INSERT statement from Query Analyzer, do you get the
> same error message? In such case, pay attention on whether the error
> message includes a procedure name.

Another thing is if you're using Access as your client, even though
it's basically just sending the SQL through to the backend database,
sometimes it'll parse it first, so it's a good idea to surround all
your field-names with [square brackets] to make it clear you're
talking about a field.

Insert or Change Identity

How can I insert or drop identity for a column from the query analyser?I don't think you can...

You can create a new column, insert the data from it, drop the column, add a new column with the old name, and move the data back...don't know if you can rename a coulmn..

Gotta look more closley at ALTER..

anyway, I was hacking around with this

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 Char(1))
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

ALTER TABLE myTable99 ALTER Column Col1 int
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...|||Originally posted by Brett Kaiser
Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...

I know it is possible in Enterprise Manager but I like to do it through Query Analyser.But the question is how?|||You can add a new column which has the identity property.
You can drop the column.

Don't think you can remove the identity property though.
e-m will probably create a new table and copy the data - it tends to do that even if it doesn't need to.

As Brett says you can create a new column, copy the data and drop the old.