Get all unused numbers in range

What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.

For instance say I have the following data in table:

Account Name
--- ---
50100 Test1
50105 Test2
50106 Test4
..
..
..
I should see the results:
50101
50102
50103
50104

because 50101-50104 are available account numbers since not currently in
table.

Thanks."Jason" <NO-SPAM-xyz@.msn.com> wrote in message news:525Ka.43802$hI1.7823@.nwrddc01.gnilink.net...
> What I am trying to do is get all of the records in a table that are out of
> sequence so I know which account numbers I can reuse. I have a range
> of account numbers from 50100 to 70100. I need to know which account
> numbers are not stored in the table (not currently used) so I can use.
> For instance say I have the following data in table:
> Account Name
> --- ---
> 50100 Test1
> 50105 Test2
> 50106 Test4
> .
> .
> .
> I should see the results:
> 50101
> 50102
> 50103
> 50104
> because 50101-50104 are available account numbers since not currently in
> table.
> Thanks.

Here's a UDF that will fill in all missing integers between a
lower bound and an upper bound, both inclusive. By default,
the lower bound is 50100 and the upper bound is 70100.

CREATE TABLE Accounts
(
account_id INT NOT NULL PRIMARY KEY,
account_name VARCHAR(10) NOT NULL
)

CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)

INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d + 10000 * TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands

CREATE FUNCTION IntervalGaps
(@.lower INT = 50100, @.upper INT = 70100)
RETURNS @.missing TABLE (n INT NOT NULL PRIMARY KEY)
AS
BEGIN
IF @.lower > @.upper
RETURN
INSERT INTO @.missing (n)
SELECT @.lower + I.n
FROM (SELECT MIN(account_id)
FROM Accounts) AS A(least_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A.least_id - @.lower
INSERT INTO @.missing (n)
SELECT A1.account_id + I.n + 1
FROM Accounts AS A1
INNER JOIN
Accounts AS A2
ON A2.account_id > A1.account_id + 1 AND
NOT EXISTS (SELECT *
FROM Accounts
WHERE account_id > A1.account_id AND
account_id < A2.account_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < A2.account_id - A1.account_id - 1
INSERT INTO @.missing (n)
SELECT A.greatest_id + I.n + 1
FROM (SELECT MAX(account_id)
FROM Accounts) AS A(greatest_id)
INNER JOIN
NonnegativeIntegers AS I
ON I.n < @.upper - A.greatest_id
RETURN
END

-- Sample data
INSERT INTO Accounts (account_id, account_name)
SELECT 50102, 'test1'
UNION ALL
SELECT 50105, 'test2'
UNION ALL
SELECT 50106, 'test4'

SELECT n AS account_id
FROM IntervalGaps(DEFAULT, DEFAULT)

account_id
50100
50101
50103
50104
50107
50108
...
70100

Regards,
jag|||SET NOCOUNT ON

declare @.lowwer as int
declare @.upper as int

declare @.cur as int

set @.lowwer = 50100
set @.upper = 50200

set @.cur = @.lowwer

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing
--temp table to hold list of missing
create table #missing (
Missing int
--,NotMissing int
)

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Account]'))
Drop table #Account
-- temp table to hold your range
create table #Account(
Account_number int
)

insert into #Account (Account_number)
select Account from [your table]
where inv_number like '[0-9][0-9][0-9][0-9][0-9][0-9]'
and inv_number between @.lowwer and @.upper
-- and any other filter you think would be handy

While( @.cur between @.lowwer and @.upper)
Begin --while

if not exists (select Account_number from #Account
where inv_number = @.cur)
Begin --if
insert into #missing (Missing) values (@.cur)
End --if
--else
--Begin --else
--insert into #missing (NotMissing) values (@.cur)
--End --else

set @.cur = @.cur + 1

End --while
-- return the results
select * from #missing
--clean up
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Inv]'))
Drop table #Inv

Get all the records of the table from the SQL Server Database

Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.

Does anyone have a solution for me? Or there be other way?
I am always waiting for ur suggestion.You can take the backup of the database and then restore. This will restore everything (Table structure / SP's etc along with data.

Check the article for backup/restore:

http://vyaskn.tripod.com/backup_and_restore_with_sql_litespeed.htm|||Thank a lot.

Get all records from Last Two Days?

Hi Everyone,

I use the following to get records from the last two days in MySql:
where date_entered <= curdate() and date_entered >=
DATE_SUB(curdate(),INTERVAL 2 day)

I'm looking to do the same in MS-Sql server but I'm just not getting it.
I've got this so far which does not work:
where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2
day)

then I tried this:
WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)
Essentially, I need all records from the last two days.

Any help or guidance in this matter would be greatly appreciated.

-JohnyBTry:

WHERE hit_date >= GETDATE() - 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John" <nothanks@.nope.com> wrote in message
news:7JKdnbE0F6Nc-tjZRVn-uQ@.adelphia.com...
Hi Everyone,

I use the following to get records from the last two days in MySql:
where date_entered <= curdate() and date_entered >=
DATE_SUB(curdate(),INTERVAL 2 day)

I'm looking to do the same in MS-Sql server but I'm just not getting it.
I've got this so far which does not work:
where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2
day)

then I tried this:
WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)
Essentially, I need all records from the last two days.

Any help or guidance in this matter would be greatly appreciated.

-JohnyB|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:4Sd1g.5357$wK1.227431@.news20.bellglobal.com.. .
> Try:
> WHERE hit_date >= GETDATE() - 2
>
> --
> Tom

Yes. That's it.

Thanks!

JB

> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "John" <nothanks@.nope.com> wrote in message
> news:7JKdnbE0F6Nc-tjZRVn-uQ@.adelphia.com...
> Hi Everyone,
> I use the following to get records from the last two days in MySql:
> where date_entered <= curdate() and date_entered >=
> DATE_SUB(curdate(),INTERVAL 2 day)
> I'm looking to do the same in MS-Sql server but I'm just not getting it.
> I've got this so far which does not work:
> where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL
> 2
> day)
> then I tried this:
> WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>
> Essentially, I need all records from the last two days.
> Any help or guidance in this matter would be greatly appreciated.
> -JohnyB|||Use the dateadd (Or Datediff) for that:

hit_date >= DATEADD(d,-2,GETDATE()), keep in mind that this will
substract the days including the current time, so a 04/19/2006 1:09
will result in 04/17/2006 1:09.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||John (nothanks@.nope.com) writes:
> I use the following to get records from the last two days in MySql:
> where date_entered <= curdate() and date_entered >=
> DATE_SUB(curdate(),INTERVAL 2 day)
> I'm looking to do the same in MS-Sql server but I'm just not getting it.
> I've got this so far which does not work:
> where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL
2
> day)
> then I tried this:
> WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>
> Essentially, I need all records from the last two days.

Since you appear to be trying out syntax at random, I must ask: did
you ever consider to consult Books Online?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do you mean yesterday and today's data only?

Select * from yourtable
where datecol>=Dateadd(day,datediff(day,o,getdate()),-1)

Madhivanan

Get all records from 1 table

I have a view with the following FROM clause. I want to be able to get ALL
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql

Get all records

Hi I have a table route with

routeid name

1 a

2 b

3 c

4 d

5 e

..

10 j

exExposure

caseid routeid

200 1

300 2

..

Now I have written a query which will join some other tables to get the results

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

When i run this query I am getting only the routes which have the values exist in exExposure table.If for some routes like i, j I don't have corresponding casesids.But i need to get all the routes which exist in Route table even they don't have caseids.

For this i am trying like this:

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

left join exExposure e on c.caCaseID=e.caCaseID

Left Join Route er on er.RouteID=e.RouteID

where --conditions

Union

Select er.RouteID,er.Name,0,0

From Route r

where r.routeID Not IN (Selelct RouteID From above Selelct query with the same where condtions and joins)

Any other better way for getting the unmatched routes other than this.

Thanks in advance.

I'm not sure what your question is, but I'll take a guess.

If you want the original query to contain all Routes (Route table), then name the Route table as the FIRST table in the series of joins, OR, in the current query, use a RIGHT JOIN with Routes. Either option 'should' give you what you seek.

|||

You can get your desired result using the following query..

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from cacase c

Left Join exExposure e on c.caCaseID=e.caCaseID And {all Your exExposure table based conditions}

Left Join Route er on er.RouteID=e.RouteID And {all your Route table based conditions}

where {other conditions}

|||

Hi,

I placed all the ExExposure table condion after the exExposure table join this is for Route table also. But now also it is giving Routes which have the CaseID s only. Any other way.

Thanks in advance.

|||

The following query will be help you...

Code Snippet

select

er.exRouteID as RouteID,er.[Name] as route,

'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),

'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),

from Route er

Left Join exExposure e on er.RouteID=e.RouteID

Left Join cacase c on c.caCaseID=e.caCaseID

where other condition

Get a list of changed records in a table

Is there a way to get a list of records that have changed since a specific
Date/Time?
I could create a trigger and a new table and work off that data but I have a
lot of tables I want to query so I don't necessarily want to create triggers
for each one.You cannot get this unless you have a datetime column on the table that you
update when data changes. or a triggering mechanism to store changed data.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>|||> Is there a way to get a list of records that have changed since a specific
> Date/Time?
No, SQL Server does not keep this information for you automagically.
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
If you allow ad hoc access to your tables, then this is how it's done, I'm
afraid. If you deny INSERT/UPDATE rights to the table and force those
actions through stored procedures, then you could perform the logging within
a stored procedure, which at least avoids some of the problems with doing
this in a trigger. But for DELETE you would have to log to a separate table
or use a trigger.
While it doesn't help you today, SQL Server 2008 will have several options
to make this easier... from change tracking and change data capture to the
extreme of audit all actions.
A|||You could use the new OUTPUT clause to automatically pump the pre-existing
data into another table and then query that 2nd table
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>

Get 10 records fro each group

Hi
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

Geographical data in SQL Server

Hi,
Is there any way to use SQL server to store and query geographical data? For
example, be able to query all the records that fall withing certain
geographical poligon defined by lat/lon coordinates?
Thanks.
You're talking about GIS. You could store the data in SQL but you're
probably going to use MapInfo or ArcInfo to perform the geographical
queries..
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.codecomments.com ***

Geographical data in SQL Server

Hi,
Is there any way to use SQL server to store and query geographical data? For
example, be able to query all the records that fall withing certain
geographical poligon defined by lat/lon coordinates?
Thanks.You're talking about GIS. You could store the data in SQL but you're
probably going to use MapInfo or ArcInfo to perform the geographical
queries..
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.codecomments.com ***

Generating Sequence Number....

Hello folks,
I have a table where the records are like followings.

ID Value
-----
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

ID Value SeqId
----------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1

Thanks,
Rahul JhaI cannot remember if you need to declare a length for a varchar column when used in a convert expression. Well, you'll either have 1 character or the correct number, so an easy test.

Your question about the actual expression appears a little too easy, which makes me feel I might have misread it. From what I understand it is that you need to accomplish, here is the basic idea behind a solution:

select
(convert(nvarchar, ID) + ' ' + Value) as 'NewSeq'

Of course you will need to modify the formatting to meet your exact requirement, but it does nonetheless appear to be little more than a straight forward concatenation of two fields.

Regards,|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.


Thanks,
Rahul Jha|||I wish I could have done that Robert... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.

Thanks,
Rahul Jha

If that is the case, then your question does not make sense from what you have explained. Either there is more to the the problem than has been mentioned or I've really misread it.

Your question asked how to concatenate a numeric and a non-numeric column to produce a single column. In any database, such a concatenation has the possibility to produce a non-numeric result. In your example, each and every row, will produce this kind of result.

Yet you now say that the destination column is of type INT, which makes your question impossible to answer.

Please do excuse me if I haven't understood, but the problem appears to be incredibly simple. Unless of course you want to store the concatenation of the numerical representation of the characters in the value column with the numeric value in the ID column. Could this be what you are trying to achieve?

My apologies, but I found it very difficult to ascertain your requirements other than what I assumed above, from your original question.

Regards,|||You are right Robert. Mistake was from my side only. Actually i should'nt have written (ID + Value). Anyways, what I am looking for is a new column named SeqId (INT) that will contain the data as per the column ID and Value. That means for a particular combination of ID & Value the value will be 1 and then for the same ID but different set of Value the data will be 2 and then for the next set of value for the same ID it will be 3 and so on........ And the same process will start for the next ID......

Thanks,
Rahul Jha|||No, you did not make a mistake at all. I just did not read the question completely, specifically the sample output that you provided. I therefore apologise for my above comments, they are not really relevant.|||You will need access to a temporary row identifier of some description so that you can distinguish between rows that have identical values in each of the columns that are to be used to determine the value for the SeqID column.

A common approach to these types of problems in Oracle is to use the Rownum function in conjunction with a defined ordering of the set. If you are using SQL Server 2005, you can use the new ROW_NUMBER function to achieve this purpose. If you're using SQL Server 2000, the easiest approach would be to temporarily insert an identity column named rowID or similar, and of course ensure that the values within are unique.

Once you have access to a rowID using one of the above methods, the SELECT query becomes a trivial self-join. The following code shows how to produce the result set that you described, using Oracle and the rownum function.

SQL> select * from myTable;

ID VALUE
---- --
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa

13 rows selected.

select
t1.id,
t1.Value,
count(t2.rn) SeqID
from
(
select
id,
value,
rownum as rn
from
(select id, value from myTable order by id, value)
) t1

inner join
(
select
id,
value,
rownum as rn
from (select id, value from myTable order by id, value)
) t2
on t2.rn <= t1.rn
and t1.id = t2.id
and t1.value = t2.value

group by
t1.id,
t1.value,
t1.rn
order by
t1.id,
t1.value,
t1.rn;

This query will produce the following result set.

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

I do know that you can use a multiple table select query as the criteria for an update query, but I can't at this moment remember the exact syntax. However the Select query is what's important, and what I've supplied above will produce the set that you need.

Regards,|||ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 dd 1
2 dd 2
2 pp 1
3 qq 1
4 aa 1

13 rows selected.

This is not the resultset that i am looking for...... I am looking for something like this.......

ID VALUE SEQID
---- -- ----
1 aa 1
1 aa 1
1 aa 1
1 bb 2
1 bb 2
1 bb 2
1 bb 2
1 cc 3
2 dd 1
2 dd 1
2 pp 2
3 qq 1
4 aa 1

I am again sorry for my putting across a wrong question...... I got to know this only when you gave me the answer......... Sorry again guys.......

Thanks,
Rahul Jha|||No problem. Nothing gets me more excited than a continuously changing business requirement. ;)

One moment whilst I excuse myself from talking to my wife , to rewrite the SQL. However, could you just confirm that you are now positive that what you supplied above is the result set that you want?|||:) :) :)

I am 100% sure of my requirement this time.

Thanks,
Rahul Jha|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.|||My DB is SQL SERVER 2K (Unfortunetly)

Thanks,
Rahul Jha|||You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

Whatever solution you're most comfortable with I guess.Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.|||Actually it was that very approach that allowed me to save my previous firm millions of pounds in manual labour costs.

"Rob, we need to improve the efficiency of this process. Could you please take a look"

"Ok, I've concluded the best approach to take is to just delete the process. In other words if we do not run it, the execution time will be zero."

"Oh good heavens, why didn't I think of that. This is what we need in this department, people who can think outside of the box."

"Exactly! Now that will be 250 pounds please."|||Although OVER is more efficient. Not in 2k of course... unless you count not running at all as efficient.
It is, check the IO and TIME statistics! ;)|||George,

I don't believe that comment is logically valid.

To

Although OVER is more efficient..

You replied with

It is, check the IO and TIME statistics!

You appear to be agreeing with the previous post. Unless it's an earth shattering improvement, should we really worry to check the IO and Time statistics.

Alternatively,

Not in 2k of course

You replied with

It is, check the IO and TIME statistics!

I think somebody forgot to read the rest of the sentence while remembering the contents of an earlier post, which stated that the OVER() analytical function is not available in 2000 and hence the running time would be zero ;)

Regards,|||Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()

Just to clarify, the original poster did make a significant change to the initial requirements, subsequently serving to trivialize the problem, between the first example given and the most recent example that was supplied before you made reference to the analytical functions in SQL Server 2005. ;)

Regards,|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().|||We know Rahul well - par for the course ;)

At least both requirements are equally trivial using OVER().

I would actually say that the first requirement is a little more involved than the second, for the reason that for the first requirement the poster asked that the SeqID column contain sequentially incrementing values for all rows, including those that contained identical values for each of their column values.

In contrast, the second requirement relaxed this point. The values for the SeqID column can be non-unique, and hence the comparison can be applied directly to the two static columns of the set.

Regards,|||I should have shortened the quote to

unless you count not running at all as efficient.

To make my repsonse make more sense :p

It is, check the IO and TIME statistics! ;)|||Touch

Indeed.|||I'm pretty sure it is trivial. I love this little addition to the SQL syntax (although full support in SS would be nice).

Row_number () OVER (partition by col_1, col_2 order by col_2)|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?|||Does anybody have any sweetener?

I didn't think SQL Server 2005 supported the Row_Number function as an analytical function that could be used in the way you've described in your post.

Had it not been available then... you can see my point.|||Anyway - is Rahul still waiting for his solution or did we (... Robert) sort it for him?

I'm not sure. I actually ceased to continue in actually solving the problem after the requirements changed again. Though had I known about the use or Row_Number with the OVER() clause then I most probably would have completed the solution.|||I am interested not as much with the potential solution to the OP (no offense, as you guys make it seem more exciting than being naked in a sack of kittens!), as I am with the requirement that Rahul is faced with in order to need to make the database change he seeks to make.

Often those project requirements are a great source of amus...errr...interest in his posts, though often we are not given visibility to them until the third or fourth requirement change undergone in one of these threads.|||Like the changing requirements in these threads is any different from the way things work in the "real world".

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).|||Like the changing requirements in these threads is any different from the way things work in the "real world".and the real world mirrors our little microcosm by bitching about them same as us :)

Oh, and no offense, Cowboy, but I hope I never see you quite that excited ;-).are you sure? I have a video on UTube! :)|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha|||I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Unfortunately my '(' key isn't working, so I'm unable to provide the answer.

Regards,|||Guys where is the solution / suggestion............?

Thanks,
Rahul Jha

Rahul,

If you you a tone of voice that is so demanding, would you not agree that people may be a little less inclined to offer assistance?

P.S. See my above post for the answer.

Regards,|||I am sorry Robert If I sounded rude in my sentence..... It was'nt intentional.....

Thanks,
Rahul Jha|||No problem. I understand your intentions, but other people may not.

So the problem has been resolved. That's good news.

Regards,|||But Robert I am unable to get the solution from what you have suggested in the 1st page.... What you have suggested was for the requirement that I have mentioned during the begining of the thread.

Thanks,
Rahul Jha|||Rahul,

Please see my earlier post, an extract of which is provided below.

I already supplied the answer in a post reply on the first page. You just need to modify the ordering condition, which is the only true variable in these kinds of problems.

Regards,|||An admirable effort, r123456...

one can lead a horse to water...|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha|||I am extremely sorry Robert. But am unable to figure out the solution (Required Query).

Thanks,
Rahul Jha

Apologies Rahul, but this thread is now closed following two days of inactivity from the original poster, which gave sufficient reason to believe that the question had been resolved.

Regards,|||I was held up at some other frontier...... And in the mean time, gave a try to resolve this according to your suggestion..... That was the reason couldn't reply to the thread. And there was saturday and sunday between...

Thanks,
Rahul Jha|||OK, you've defeated me there with the point about Saturday and Sunday. Could you please post your modified version of the query I supplied earlier that you are using?|||ALTER TABLE TempTable ADD INDENTITY_COUNTER INT IDENTITY
GO

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(*) FROM TempTable TT
WHERE
TT.ID = TempTable.ID AND
TT.VALUE = TempTable.VALUE AND
TT.INDENTITY_COUNTER <= TempTable.INDENTITY_COUNTER
)

ALTER TABLE TempTable DROP COLUMN INDENTITY_COUNTER
GO

Thanks,
Rahul Jha|||I don't see why you need to create the IDENTITY_COUNTER column, the values for the SeqID column can be generated from the existing two columns that you have.

To correct your query you will need to make the following amendments to the query. Firstly you will need to remove the identity_counter column and indeed references to it from your query. Secondly you will need to modify your condition expressions to include the less than or greater than operator for one of your columns, and thirdly, the distinct keyword will need to be used with the count expression.|||Robert, I need the INDENTITY_COUNTER column to get the value for the SeqId column. That is the backbone of the query...... Even you have suggested the same in your query via ROWNUM keyword.

Thanks,
Rahul Jha|||No Rahul, that is not what I said. I indeed did make reference to the IDENTITY_COUNTER column as being one option to use in writing a query to meet your initial requirements, in the absence of the availability of SQL Server 2005's analytical functions. However if you will recall, you later changed your requirements such that the values for the SeqID column needn't be unique. This slight change removes the need for the identity_column. The requirement is now to populate the SeqID column with the count of distinct rows that have a value for the VALUE column which is less than or equal to the corresponding value of the current row.

Therefore to correct your query you will need to change one of your conditional operators to check for less than or equal to, and modify your count function to return only the count of distinct records as determined by the columns mentioned above.

Regards,|||Will it be in the lines of this........?

UPDATE TempTable
SET
SEQID = (
SELECT COUNT(DISTINCT ID, VALUE) FROM TempTable
WHERE
[SOMECONDITION]
)
GO

Thanks,
Rahul Jha|||Be Back From Lunch.... :-)

Thanks,
Rahul Jha|||Oh, I give up!|||Did I say anything which I was not suppose to.....?

Thanks,
Rahul Jha|||No, not at all. The point is that you haven't really said enough for me to be able to help. I and other posters have supplied a comprehensive explanation of how to develop a solution. You just need to carefully read the posts and amend your current query to address the considerations that have been mentioned to reflect the change in your requirements.|||I am sorry again Robert. Couldn't able to figure out the solution. But anyways, thanks to you guys for the kind help......

Thanks,
Rahul Jha

Generating reports on very large and "live" databases

Hello All,
I am working on a application which has a table with 100 million
records.
A background process keeps inserting 5 rows per second into this
table.
My client is generating reports from that table. They are interested
in seeing reports with real time data. This means that as soon as a
record is inserted into the table, the reports should process that
record as well.
At the max around 20K records are processed to generate a report. When
this happens it takes simply 30 min to generate a report. Upon
analysis I find that since the table is so huge, the indexes on those
table is also very huge.
What can I do to fix this, I have thought about denormalizing the
table. But the some programmers say that picking up data from one
table is better because doing joins on multiple tables will be even
slower.
Another approach is to bring in a data warehouse but I don't know much
about this except what I learnt in MSDN session about creating of data
cubes. But I suppose cube can be created only when data is static. but
in my case new records are inserted every second and they are to be
included in the report.
The 3rd approach is that I create report specific tables and create a
trigger (or a C programm which polls for changes in main table) and
every time new records are inserted into the main table, I preprocess
them. Then when the users make a request for the report I generate my
report from the preprossed table.
But I feel the trigger will be fired to many times and if the number
of reports are significant (> 35) then trigger/C program could become
a bottleneck itself.
What should I do? it is such a tricky problem.
Please help me and give me some advice. Thank you for your help.
regards,
Abhishektable size (or row count) is irrelevent unless a
table/index scan is involved.
5 rows/sec insert is a negligible load on the system.
however, a report that involves 20k rows with good indexes
should not take 30min to run.
1) what is the query,
2) what are the indexes on this table
3) what does the execution plan show?
(indexes used, type of operation, rows involved, costs)
>--Original Message--
>Hello All,
>I am working on a application which has a table with 100
million
>records.
>A background process keeps inserting 5 rows per second
into this
>table.
>My client is generating reports from that table. They are
interested
>in seeing reports with real time data. This means that as
soon as a
>record is inserted into the table, the reports should
process that
>record as well.
>At the max around 20K records are processed to generate a
report. When
>this happens it takes simply 30 min to generate a report.
Upon
>analysis I find that since the table is so huge, the
indexes on those
>table is also very huge.
>What can I do to fix this, I have thought about
denormalizing the
>table. But the some programmers say that picking up data
from one
>table is better because doing joins on multiple tables
will be even
>slower.
>Another approach is to bring in a data warehouse but I
don't know much
>about this except what I learnt in MSDN session about
creating of data
>cubes. But I suppose cube can be created only when data
is static. but
>in my case new records are inserted every second and they
are to be
>included in the report.
>The 3rd approach is that I create report specific tables
and create a
>trigger (or a C programm which polls for changes in main
table) and
>every time new records are inserted into the main table,
I preprocess
>them. Then when the users make a request for the report
I generate my
>report from the preprossed table.
>But I feel the trigger will be fired to many times and if
the number
>of reports are significant (> 35) then trigger/C program
could become
>a bottleneck itself.
>What should I do? it is such a tricky problem.
>Please help me and give me some advice. Thank you for
your help.
>regards,
>Abhishek
>.
>

generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generating a primary key

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.

What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.

Regards

Sara

I personally prefer generating my key values inside the pipeline. For your scheme you would need to write some bespoke code to generate the incrementing value. Use a Script Component to host this code, and output a new column with the value.

Here is an example of this pattern in use - http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/, just tweak teh code to give you your values.

|||

Wow, thank you very much, I followed your example (ignoring the letters, trying with incremental numbers only), it works and it works well!

Just have to play around to make it work with letters too.

Thanx again!

|||

Sara,

Primary keys should never contain logic within them. Why do you need the letters? Using integers, you can achieve the correct order of SCD changes for a given business key, so I don't see the need for letters at all.

|||What do you exactly mean by 'the correct order of SCD changes for a given business key'?|||

Sara4 wrote:

What do you exactly mean by 'the correct order of SCD changes for a given business key'?

I was just trying to make an assumption as to why you'd want letters in your key. With integers, you could select a business key and order by the primary key to see the order of the changes as they occurred. Granted, you could do that with your approach as well, but you're actually going to use more storage using your method (based on your example above) versus using a 4-byte integer field.

generate the insert script from table of SQL Server

Dear All,

I would like to generate the insert scripts of the 500 records for the later software initialization. Could you suggest some software to do this? Thanks.

Alex

For information

the environment are SQL Server 2000

Try Squirrel.

http://search400.techtarget.com/tip/1,289483,sid3_gci1165699,00.html

Generate Scripts for existing records in a table

I can use Generate Scripts tool to get scripts for tables, procedures etc. But now I have records in tables which I want all "Insert into ..." queries from sqlserver. My co-worker said I have to manually type all these lines for my data.sql. Do I have to? I guess there must be a way...

Thanks,

LiliTry using DTS (Data Transformation services) which will allow you to recreate and / or rename the table and also lets you populate with original data.

The fastest way to recreate tables as you can recreate your entire database if you want to.

:p|||DTS is awsome :) but I need to deliver data.sql (of course with all other *.sql files) to the client so that they can install and drop database any time they mess it up :P|||It sounds like you want to give the client some base data. Could you not just BCP the data out into files and then create a command file that would truncate the tables and then BCP the data back in?

Or do you need to create the INSERT statements?
Example:INSERT myTbl (col1,col2,col3) VALUES ('xxx','aaa',123)|||Yes! base data. I need to insert statements. I just typed up some, but I'm wondering whether I can use a tool to make life easier :)

and guru, could you read my post about "Create My Own Log (for functionality) "? Thank you!

Lili|||Following sql statement will help you to generate INSERT STATEMENT for your base table

Table Name : BaseTable
fields
BName varchar(20)
dt smalldatetime
val int

select 'Insert into BaseTable VALUES (' + '''' + bname + '''' + ','
+ '''' + convert(varchar(10),dt,101) + '''' + ','
+ '''' + convert(varchar(10),val) + '''' + ')'
from basetable

handle NULL part by using ISNULL function

All the best|||Wow! Good idea. Silly me :p Thanks,

Lili|||You know sometimes I get carried away. I wrote a stored procedure that will generate all the INSERT statements for a given table. I've attached the proc, it worked for me on 2000.

Example on pubs
exec usp_CreateInsert discounts

OUTPUT:
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Initial Customer',NULL,NULL,NULL,10.50)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Volume Discount',NULL,100,1000,6.70)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Customer Discount','8042',NULL,NULL,5.00)|||//@.@. where is the stored procedure?|||It must have stoppped me because the extension of the file was '.sql', so I had to change it to '.txt'|||wow, you are real sql guru //admire

thank you for your help and sharing :)

Have a nice weekend!

Lili

Generate Reports

How I make to generate reports of the replicated data ?
The report must have the format : origin,destination,datetime,table name,
and records of the table.
Thanks
FDB,
in order to answer correctly, I'd like to know a bit more about:
(1) what type of replication you are using.
(2) origin is presumably the first computer to add/update the record? If so
then this will need to be an additional field with a default.
(3) destination - is this any different than the table you're examining?
(4) datetime - is this the time the record was originally added ot the time
the record was added due to replication
Is this report a list of records or statistical aggregates?
TIA,
Paul Ibison
|||I am using Merge replication.
I have two sql servers in distinct sites, and each site make changes to
data independently.
This report is a list of records, for I check which data they had been
inserted in both databases.
The purpose is I to know if all the data had been transferred correctly.
Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eH%23OpOXJEHA.3596@.tk2msftngp13.phx.gbl...
> FDB,
> in order to answer correctly, I'd like to know a bit more about:
> (1) what type of replication you are using.
> (2) origin is presumably the first computer to add/update the record? If
so
> then this will need to be an additional field with a default.
> (3) destination - is this any different than the table you're examining?
> (4) datetime - is this the time the record was originally added ot the
time
> the record was added due to replication
> Is this report a list of records or statistical aggregates?
> TIA,
> Paul Ibison
>
|||OK - thanks for the explanation. In that case you might like to look at "validating data" in BOL. There is a section towards the bottom of the page on merge replication. You can choose to use rowcounts only, rowcounts and checksums, or rowcounts and compa
ring binary checksums (all Subscribers must be running SQL Server 2000 to use this option). There is a replication alert which fires if validation is failed (Replication: Subscriber has failed data validation). This can be set up in EM, using stored procs
or using the agent parameters - details in BOL.
HTH,
Paul Ibison

Generate insert script

does anyone know if there is a way to generate an insert script based
on the records in a table? I want to distribute the contents of a
couple of tables to customers without having to use BCP or DTS.

Greetings Sjaak"Sjaak van Esdonk" <sjaakvanesdonk@.hotmail.com> wrote in message
news:7479e65c.0404050347.1105649b@.posting.google.c om...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

http://vyaskn.tripod.com/code.htm#inserts
http://www.rac4sql.net/objectscriptr_main.asp

Simon|||Thanks Simon, thats exactly what i needed! This is going to save me lots of time !|||sjaakvanesdonk@.hotmail.com (Sjaak van Esdonk) wrote in message news:<7479e65c.0404050347.1105649b@.posting.google.com>...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

Hi,
use SQL Scripter (http://www.sqlscripter.com) to create Insert, Update
or Delete scripts ...

Thomas