Inserting a Count into a Table

I am inserting data from one table into another. To simplify:

Table 1 has columns:
1.A, 1.B, 1.C, 1.D

Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag

where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.

Therefore, if Table 1 is

x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z

then, table 2 is

x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3

anyone help me w/ the sql on that ?

i was thinking of putting a variable in the select statement, but was unable to increment it.

thanksWhat do 2.A and 2.B hold? Is 1.A and 1.B relevant?|||This could be done in a single select statements, but I refuse to think about it further until you explain why you would want to do such a loopy thing.|||Try this but I not sure my coding.
INSERT INTO table1 SELECT DISTINCT *FROM table2|||a counter, not a count

what sequence governs this counter, A and B?

could you perhaps explain why you want this weird data?|||Try this

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
Select Distinct *,Tag=(Select count(*) from (Select Distinct D from @.t) T1 where T1.D<=T2.D) from @.t T2

Madhivanan|||madhivanan, very nice try, but not quite right

add the following to your test data and see what happens :) :) :)

insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')|||r937

try this with different combinations

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Select Distinct *,Tag=(Select count(*) from (Select Distinct * from @.t) T1
where T1.A+T1.B+T1.C+T1.D<=T2.A+T2.B+T2.C+T2.D) from @.t T2

Madhivanan|||sorry, that's not right either ;)

add this to your data and see what happens --

insert into @.t values('y','y','x','x')|||r937,

Can you post the expected outcome for these data?

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Madhivanan|||sure

x x x x 1
x x x x 2
x x x x 3
x x x y 1
x x x z 1
x x y y 1
x x z y 1|||Purpose of craziness: i want to order the data when i select from that table.

for the below
Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

the new table should have:

x-x-x-x-1
x-x-x-y-2
x-x-z-y-3|||First, I don't understand why your output isn't:

x-x-x-x-1
x-x-x-y-2
x-x-x-z-3
x-x-y-y-4
x-x-z-y-5

Second, if you are relying on the order of the data in the table for your logic, you are letting yourself in for a heap-o-hurtin'.|||aargh, i think my understanding of this has been wrong all along

it should be like in post #13!!

okay, what about if you add these rows, then what do you get --

insert into @.t values('b','b','x','y')
insert into @.t values('c','c','x','z')|||oh ya..
1. the order should be that...(sorry, it is a friday)

2. i want it ordered as such when i do an extract and display into a report.l

thanks!
C

Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

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 checkbox value into bit field sql server 2000

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

This is probaly the easiest question you've ever read but here goes.

I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.

Here is my code:

Sub AddSection_Click(Sender As Object, e As EventArgs)
 Dim myCommand As SqlCommand
 Dim insertCmd As String
 ' Build a SQL INSERT statement string for all the input-form
 ' field values.
 insertCmd = "insert into Customers values (@.SectionName, @.SectionLink, @.Title, @.NewWindow, @.LatestNews, @.Partners, @.Support);"
 ' Initialize the SqlCommand with the new SQL string.
 myCommand = New SqlCommand(insertCmd, myConnection)
 ' Create new parameters for the SqlCommand object and
 ' initialize them to the input-form field values.
 myCommand.Parameters.Add(New SqlParameter("@.SectionName", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.SectionName").Value = Section_name.Value
 myCommand.Parameters.Add(New SqlParameter("@.SectionLink", SqlDbType.nVarChar, 80))
 myCommand.Parameters("@.SectionLink").Value = Section_link.Value
 myCommand.Parameters.Add(New SqlParameter("@.Title", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.Title").Value = Section_title.Value
 If New_window.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 1
 End If
 If Latest_news.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 1
 End If
 If Partners.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 1
 End If
 If Support.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 1
 End If
 myCommand.Connection.Open()
 ' Test whether the new row can be added and display the
 ' appropriate message box to the user.
 Try
 myCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & insertCmd
 Catch ex As SqlException
 If ex.Number = 2627 Then
 Message.InnerHtml = "ERROR: A record already exists with " _
 & "the same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please " _
 & "ensure the fields are correctly filled out"
 Message.Style("color") = "red"
 End If
 End Try
 myCommand.Connection.Close()
 BindGrid()
 End Sub

Any response would be appreciatedYou're right; it's easier than you think. Try this:

myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
myCommand.Parameters("@.Partners").Value = Partners.Checked

|||thanks a lot and sorry for the late reply that worked

Inserting a block of numbers sequentially into a table

I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7, bu
t
don't have the experience to understand this enough. I also looked in
previous questions on this group, but find I still need help. I need to
insert a series of MSR (Medical Service Record) numbers into a table of
appointments over a selected date range. The last used MSR is recorded in
this table in column LastMSR:
CREATE TABLE [UserVars] (
[LastMSR] [int] NOT NULL ,
[RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
Beginning with LastMSR+1, I want to insert numbers sequentially into the
column MSR of the Appointments table (see below), selecting rows where MSR =
0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
CREATE TABLE [Appointment] (
[APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[APPT_DATE] [datetime] NULL ,
[RESOURCE_ID] [int] NOT NULL ,
[DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_TIME] [datetime] NULL ,
[DURATION] [datetime] NULL ,
[STATUS] [smallint] NOT NULL ,
[CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSR] [int] NOT NULL ,
[ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
DEFAULT (getdate()),
[ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Once I'm done, I want to write my highest assigned MSR back to table
UserVars; however, I don't want any other user to allocate a block of MSRs
until I'm done.
Thank you...My first question is: Do you want to single thread access to the table? In
2000, do something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
In 2005, look at rownumber()
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7,
>but
> don't have the experience to understand this enough. I also looked in
> previous questions on this group, but find I still need help. I need to
> insert a series of MSR (Medical Service Record) numbers into a table of
> appointments over a selected date range. The last used MSR is recorded in
> this table in column LastMSR:
> CREATE TABLE [UserVars] (
> [LastMSR] [int] NOT NULL ,
> [RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> ) ON [PRIMARY]
> GO
> Beginning with LastMSR+1, I want to insert numbers sequentially into the
> column MSR of the Appointments table (see below), selecting rows where MSR
> =
> 0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
> CREATE TABLE [Appointment] (
> [APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [APPT_DATE] [datetime] NULL ,
> [RESOURCE_ID] [int] NOT NULL ,
> [DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [START_TIME] [datetime] NULL ,
> [DURATION] [datetime] NULL ,
> [STATUS] [smallint] NOT NULL ,
> [CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSR] [int] NOT NULL ,
> [ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
> DEFAULT (getdate()),
> [ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Once I'm done, I want to write my highest assigned MSR back to table
> UserVars; however, I don't want any other user to allocate a block of MSRs
> until I'm done.
> Thank you...|||Sorry, I don't understand. When I start, the appointments table might be
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 0
2 11/07/2005 0
etc.
If the last used MSR number (stored in UserVar table) is 20, then when I'm
done I want the appointments table to look like this:
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 21
2 11/07/2005 22
etc.
Starting with the stored LastMSR+1, each row increases MSR by 1.
"Louis Davidson" wrote:

> My first question is: Do you want to single thread access to the table?
In
> 2000, do something like:
> select 'Blue' as color
> into #testtable
> union all
> select 'Red'
> union all
> select 'Green'
> select color, (select count(*) from #testTable as t2 where t2.color <=
> #testTable.color) as rowNumber
> from #testTable
> order by 2
> In 2005, look at rownumber()
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>
>

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