Doing the same over and over in different tables

[MattHHDE]

Hi

i have to do the same operation several time on different tables.
I want to get the value of a column (baseline) from @tableName, store it
into a variable and use this to update @tableName later on.

The baseline column is a comma separated list of ids (didnt find a better
way other than to copy a line for each baseline...)

can anyone tell me how the "-- HOW TO DO THIS?" can be done?

Thanks!
Matthias

--------------------------------------------------------------
Its basically this:

--Remember current base line id
DECLARE @baseline nvarchar(50)
SET @baseline = LTRIM(str(IDENT_CURRENT('dbo.sysBaselines')))

--For entries with an existing base line
--Get existing text of the base line
DECLARE @baseLineText NVARCHAR(max)

DECLARE @cmd nvarchar(max)
SET @cmd = 'SELECT Baseline FROM ' + @tableName + ' WHERE (Baseline IS NOT
NULL and IsLatest=1)'

-- HOW TO DO THIS?
SET @baseLineText= EXEC(@cmd)

print @baseLineText

--BELOW IS YET TO BE CHANGED to handle @tableName,

--Concat old text with new base line id
SELECT @baseLineText = @baseLineText + ',' + @baseline

--Update entries with previous base line
UPDATE @tableName
SET Baseline=@baseLineText
WHERE (Baseline IS NOT NULL and IsLatest=1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Cannot update existing base lines for <Table>', 16, 4) WITH LOG
RETURN
END

--Update entries with no previous base line was set
UPDATE @tableName
SET Baseline=LTRIM(str(@baseline)),
sLatest=1
WHERE Baseline IS NULL
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Cannot add base line for <Table>', 16, 3) WITH LOG
RETURN
END

PS: I am experimenting at the moment and new to this stuff :) Any further
suggestions are very welcomed!

[Erland Sommarskog]

MattHHDE (Matt...@discussions.microsoft.com) writes:
> i have to do the same operation several time on different tables.
> I want to get the value of a column (baseline) from @tableName, store it
> into a variable and use this to update @tableName later on.

If you find that you need to do this - and it is not an admin thing
like getting the space usage - there is probably an error in the table
design. Maybe all those tables should be one single table, with one
more key.

> The baseline column is a comma separated list of ids (didnt find a
> better way other than to copy a line for each baseline...)

Beware that this breaks first normal form. Of course, there is nothing
that compels you follow any normal forms, but since SQL is designed
to work with normalised tables and comma-separated lists, any
analysis, querying or maintenance of these lists, will be a nightmare.

I don't really know what these baselines are, but probably they should
be a subtable with one row per entry.

> can anyone tell me how the "-- HOW TO DO THIS?" can be done?

You use sp_executesql. I have an article on my web site which covers
dynamic SQL in detail that my interest you:
http://www.sommarskog.se/dynamic_sql.html.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[MattHHDE]

"Erland Sommarskog" wrote:

> MattHHDE (Matt...@discussions.microsoft.com) writes:
> > i have to do the same operation several time on different tables.
> > I want to get the value of a column (baseline) from @tableName, store it
> > into a variable and use this to update @tableName later on.
>
> If you find that you need to do this - and it is not an admin thing
> like getting the space usage - there is probably an error in the table
> design. Maybe all those tables should be one single table, with one
> more key.
>
> > The baseline column is a comma separated list of ids (didnt find a
> > better way other than to copy a line for each baseline...)
>
> Beware that this breaks first normal form. Of course, there is nothing
> that compels you follow any normal forms, but since SQL is designed
> to work with normalised tables and comma-separated lists, any
> analysis, querying or maintenance of these lists, will be a nightmare.
>
> I don't really know what these baselines are, but probably they should
> be a subtable with one row per entry.

Baselines are versions of the data in the database.
If I remove the comma separated list than that would mean that the
"Baseline" table would need a column for each data type which can be part of
a baseline,,, These are over 30 and may become more later on... This does
also not sound to well...
Another idea is to create a table with "BaselineID", "Type" (Type for data
table A = 1, B = 2...) and "RefToData".
But this would mean that this table grows by N per base line were N is the
number of "Data to be baselined"...

Is there another way? OK, this is off-topic now.

Thanks for the link. I take a look into it.

cu
Matthias

[Erland Sommarskog]

MattHHDE (Matt...@discussions.microsoft.com) writes:
> Baselines are versions of the data in the database.
> If I remove the comma separated list than that would mean that the
> "Baseline" table would need a column for each data type which can be
> part of a baseline,,, These are over 30 and may become more later on...
> This does also not sound to well...

One way to address that is the sql_variant data type.

> Another idea is to create a table with "BaselineID", "Type" (Type for data
> table A = 1, B = 2...) and "RefToData".
> But this would mean that this table grows by N per base line were N is the
> number of "Data to be baselined"...

Doesn't it anyway? So, the number of rows are smaller now, but you get a
lot of commas in the table instead.