alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
Sincerely
Why use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
--

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
--
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
--
David Portas
SQL Server MVP
--

Aggregator Transform bug or feature

Hi,

I have two input columns (both DT_I4) in a column collection to a Aggregator transform. Now I am doing a group by to one and Count to another column.

To my surprise the output's column datatype is changed for Count Transform (DT_UI8) and I have to put extra Data Conversion Transfrom to get my DT_I4 datatype back.

Is this a bug or feature.

Dharmbir

Dharmbir wrote:

Hi,

I have two input columns (both DT_I4) in a column collection to a Aggregator transform. Now I am doing a group by to one and Count to another column.

To my surprise the output's column datatype is changed for Count Transform (DT_UI8) and I have to put extra Data Conversion Transfrom to get my DT_I4 datatype back.

Is this a bug or feature.

Dharmbir

Feature. The aggregated columns are "new" to the dataflow, and a count can't be negative so they use the unsigned-integer datatype. I know, I hate it too, but that's the way it goes.

Aggregate Functions - and no records

Is the following statement documented/supported?
I can't find it documented in BOL the results for Aggregate functions when
there is no input data.
If a Aggregate function gets 0 records input, it returns null.
eg.
SELECT
MAX( Field ) AS ReturnsNull
FROM
(
SELECT TOP 0 1 AS Field
) NoRecordsRebecca York (rebecca.york {at} 2ndbyte.com) writes:
> Is the following statement documented/supported?
> I can't find it documented in BOL the results for Aggregate functions when
> there is no input data.
>
> If a Aggregate function gets 0 records input, it returns null.
> eg.
> SELECT
> MAX( Field ) AS ReturnsNull
> FROM
> (
> SELECT TOP 0 1 AS Field
> ) NoRecords
It is actually in Books Online, but it was not too easy to find it. Try
Accessing and Changing Relational Data
Query Fundamentals
Advanded Query Concepts
Using Aggregate Functions
Null Values
Here is says:
If no rows meet the condition(s) specified in the WHERE clause, COUNT
returns a value of zero. The other functions all return NULL. COUNT(*),
counts each row, even if all column values are NULL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Phew :)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F6808DFCEA4Yazorman@.127.0.0.1...
> Rebecca York (rebecca.york {at} 2ndbyte.com) writes:
when
> It is actually in Books Online, but it was not too easy to find it. Try
> Accessing and Changing Relational Data
> Query Fundamentals
> Advanded Query Concepts
> Using Aggregate Functions
> Null Values
> Here is says:
> If no rows meet the condition(s) specified in the WHERE clause, COUNT
> returns a value of zero. The other functions all return NULL. COUNT(*),
> counts each row, even if all column values are NULL.

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClassCategoryFull TimePart Time
GovernmentFederal()()
State()()
Local()()
Other(Specify)()()
IndustrialLaboratory()()
Agricultural()()
Nutrition()()
Other(Specify)()()
OtherAssociation()()
Service()()
Retired()()
Other(Specify)()()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassIDint (PK)
CategoryIDint (PK)
CategoryDescvarchar(50)
Table: JobType (used for Full or Part Time)
JobTypeIDint (PK)
JobDescvarchar(20)
Table: Member_Employement
MemberIDint (PK)
ClassIDint (PK)
CategoryIDint (PK)
JobTypeIDint
Othervarchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?
Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:

> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClassCategoryFull TimePart Time
> GovernmentFederal()()
> State()()
> Local()()
> Other(Specify)()()
> IndustrialLaboratory()()
> Agricultural()()
> Nutrition()()
> Other(Specify)()()
> OtherAssociation()()
> Service()()
> Retired()()
> Other(Specify)()()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassIDint (PK)
> CategoryIDint (PK)
> CategoryDescvarchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeIDint (PK)
> JobDescvarchar(20)
> Table: Member_Employement
> MemberIDint (PK)
> ClassIDint (PK)
> CategoryIDint (PK)
> JobTypeIDint
> Othervarchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>
|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClass Category Full Time Part Time
Government Federal () ()
State () ()
Local () ()
Other(Specify) () ()
Industrial Laboratory () ()
Agricultural () ()
Nutrition () ()
Other(Specify) () ()
Other Association () ()
Service () ()
Retired () ()
Other(Specify) () ()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassID int (PK)
CategoryID int (PK)
CategoryDesc varchar(50)
Table: JobType (used for Full or Part Time)
JobTypeID int (PK)
JobDesc varchar(20)
Table: Member_Employement
MemberID int (PK)
ClassID int (PK)
CategoryID int (PK)
JobTypeID int
Other varchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:
> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClass Category Full Time Part Time
> Government Federal () ()
> State () ()
> Local () ()
> Other(Specify) () ()
> Industrial Laboratory () ()
> Agricultural () ()
> Nutrition () ()
> Other(Specify) () ()
> Other Association () ()
> Service () ()
> Retired () ()
> Other(Specify) () ()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassID int (PK)
> CategoryID int (PK)
> CategoryDesc varchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeID int (PK)
> JobDesc varchar(20)
> Table: Member_Employement
> MemberID int (PK)
> ClassID int (PK)
> CategoryID int (PK)
> JobTypeID int
> Other varchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

Advise on database layout

I'm trying to build a web application that would input user data from a
web page to a SQL Server database and I need help laying out the
database.
The web page will ask for user input on their employement status and
look something like this...
JobClass Category Full Time Part Time
Government Federal () ()
State () ()
Local () ()
Other(Specify) () ()
Industrial Laboratory () ()
Agricultural () ()
Nutrition () ()
Other(Specify) () ()
Other Association () ()
Service () ()
Retired () ()
Other(Specify) () ()
Those are radio boxes in the Full Time and Part Time columns. There
will be a text box where they can specifiy if they choose "Other".
So far my db is laid out like this.
Table: JobClass
ClassID int (PK)
ClassDesc Varchar(50)
Table: JobCategory
ClassID int (PK)
CategoryID int (PK)
CategoryDesc varchar(50)
Table: JobType (used for Full or Part Time)
JobTypeID int (PK)
JobDesc varchar(20)
Table: Member_Employement
MemberID int (PK)
ClassID int (PK)
CategoryID int (PK)
JobTypeID int
Other varchar(50) (Used if they select other)
I'm not sure if this is the best way to lay it out. I'm not sure how
to handle the situations where the user selects "other". Right now
they would type it into a text box on the web page and I would store it
in the other field in the Member_Employment table. Does anyone have
any thoughts?Is the data in the Other selection fixed?
If yes then create another table that holds the values that must exist for
Others selection and let the user select from a combobox/dropdown box.
If the values for Other is not fixed, there is no problem in permitting the
user to enter it.
Is the solution clear and does it answer your question
thanks and regards
Chandra
"mpsmith-j-quin@.excite.com" wrote:

> I'm trying to build a web application that would input user data from a
> web page to a SQL Server database and I need help laying out the
> database.
> The web page will ask for user input on their employement status and
> look something like this...
> JobClass Category Full Time Part Time
> Government Federal () ()
> State () ()
> Local () ()
> Other(Specify) () ()
> Industrial Laboratory () ()
> Agricultural () ()
> Nutrition () ()
> Other(Specify) () ()
> Other Association () ()
> Service () ()
> Retired () ()
> Other(Specify) () ()
>
> Those are radio boxes in the Full Time and Part Time columns. There
> will be a text box where they can specifiy if they choose "Other".
> So far my db is laid out like this.
> Table: JobClass
> ClassID int (PK)
> ClassDesc Varchar(50)
> Table: JobCategory
> ClassID int (PK)
> CategoryID int (PK)
> CategoryDesc varchar(50)
> Table: JobType (used for Full or Part Time)
> JobTypeID int (PK)
> JobDesc varchar(20)
> Table: Member_Employement
> MemberID int (PK)
> ClassID int (PK)
> CategoryID int (PK)
> JobTypeID int
> Other varchar(50) (Used if they select other)
> I'm not sure if this is the best way to lay it out. I'm not sure how
> to handle the situations where the user selects "other". Right now
> they would type it into a text box on the web page and I would store it
> in the other field in the Member_Employment table. Does anyone have
> any thoughts?
>|||The values for Other is not fixed and the user will enter it in a text
box on the web page. I'm just not sure I'm laying out the database the
best way to handle it.

Advice sought on adding sequential id to batch

My batches need to have some sequential ids added to them. These ids need to be system-wide.

Here is example input data. I am given the first two cols and need to generate the third:

Code Snippet

Row RowType ID

A LN LN1001

B LN LN1002

C TR TR2001

D LN LN1003

E TR TR2002

F TR TR2003

There is no relation between Row and ID except for the sequence of the records.

I have a table that looks like this:

Code Snippet

RowType MaxID

LN 1003

TR 2003

and that is where I go to get/update my latest id. (SELECT ? = MaxID = MaxID + 1 Where RowType = 'TR')

My question is this:

I will probably use a script step to do the work of obtaining and applying a new row id to each row. How do I run the requisite SQL from within a script step? Or is there a better way of doing it?

You would need to cache the current max ID by row type, in preparation for the main script execuction, so the script PreExecute event would seem like a good place. You can use an SSIS connction of type ADO.NET, and use that to get the System.Data.SqlClient.SqlConnection object, and write some stanadard .Net data access code. See http://msdn2.microsoft.com/en-us/library/ms136045.aspx for a start.

You do not want to be making that SQL call per row, so track them in code, and write back the latest values in the PostExecute event.

Alternatively you could just use the Exec SQL Task to query this data and store it in a variable.

You would use a second Exec SQL Task to write back the latest values used after the Data Flow.

|||

Cool.

That is just the sort of info I was after.

I think I will go with the query before and update after model. You've given me some excellent pointers. I guess the main thing I was concerned about was the impact on performance of doing multiple updates (running SQL for each row of data.)

Thanks,

Dylan.