I'm running this script to loop thru tables and insert the timestamp and wirespeed based upon latest updated record in table. I would like to add another field to table bc_staging called equipid and grab the file name during the loop. I can see the value in the statement write-host "Processing" $Row.name that I want to capture, but not sure how to include that in Insert, and parse the value. When I insert to equipid I want the record to look like 301-1 I want to strip out the beginning F the (-) and the string "Line" to end up with
301-1. There could be a tables like f301-line12 and that would parse to 301-12 ...
Thanks.
CREATE TABLE [dbo].[bc_staging](
[ID] [int] IDENTITY(1,1) NOT NULL,
[timestamp] [time](7) NULL,
[wirespeed] [int] NULL,
[equipid] nvarchar(25), -- NEW FIELD
CONSTRAINT [PK_bc_staging] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The table names that the loop is processing are defined like
CREATE TABLE [dbo].[F301-Line1](
[Stamp_time] [datetime] NOT NULL,
[WireSpeed] [decimal](18, 0) NULL
CONSTRAINT [PK_F301-Line1] PRIMARY KEY CLUSTERED
(
[Stamp_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
$server = "sf1"
$database = "equip"
$tablequery = "SELECT name from sys.tables where name like 'f3%'"
#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
write-host "Processing" $Row.name
$queryData = "
Insert into bc_staging
SELECT stamp_time,coalesce(wirespeed ,1) FROM [$($Row[0])]
where stamp_time >
(Select last_updated from Speed_Last_Processed)"
$command.CommandText = $queryData
$command.Connection = $connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
}
March 23, 2022 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 24, 2022 at 11:49 pm
any ideas or thoughts to make this happen?
Thx.
March 25, 2022 at 6:59 pm
I have to ask... why are you trying to use PowerShell to do this? And you have the comment "# Loop through all tables and export a CSV of the Table Data" but, with the understanding that I'm a neophyte at PoSh, I don't see where you're writing to a CSV nor I see anywhere where your reading a file. It look to me like there's no reason at all for any of the PoSh code because it seems like you're just trying to copy 1 row from one or more table to a BC_Staging table.
I suspect you're gotten no answers because what you've posted doesn't seem to make any sense. And where the heck is the supposed "file name" you're talking about supposedly coming from?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2022 at 10:34 pm
When I'm looping thru the tables in DB:
SELECT name from sys.tables where name like 'f3%'
That comment "# Loop through all tables and export a CSV of the Table Data" was a left over from something else I was doing.
The File_Name sorry wrong terminology comes from name in select query above, and it is what that value used during Insert. The Insert should have as many record from the each of the tables I read from select above and:
where
where stamp_time >
(Select last_updated from Speed_Last_Processed)"
I wouldn't even dream of doing this simple thing in PowerShell. I also can't help you if you want it done in PowerShell.
If you want it done in T-SQL, here's how I'd do it... note that this only gets the 1 row with the largest time stamp from each table. I'm not going through your PowerShell and your subsequent posts to try to figure out what you really need. This should help you do that.
DECLARE @SQL VARCHAR(MAX) = ''
;
SELECT @SQL += REPLACE(REPLACE('
RAISERROR("-----------------------------------",0,0) WITH NOWAIT;
RAISERROR("Processing <<TableName>>",0,0) WITH NOWAIT;
INSERT INTO dbo.bc_staging
([timestamp], wirespeed, equipid)
SELECT TOP 1
[timestamp] = stamp_time
,wirespeed
,equipid = SUBSTRING(REPLACE("<<TableName>>","line",""),2,128)
FROM dbo.[<<TableName>>]
ORDER BY Stamp_time DESC
;' ,'"','''')
,'<<TableName>>',obj.name)
FROM sys.objects obj
WHERE obj.name LIKE 'F[0-9][0-9][0-9]%'
AND type = 'U'
AND schema_id = 1
;
EXEC(@SQL)
;
SELECT * FROM dbo.bc_staging
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2022 at 1:29 pm
Jeff,
Thanks that works perfect, but I have another table(POST Above) that I'm trying to get ALL of the records GT timestamp from processed tables:
''>'' (Select last_updated from Speed_Last_Processed)
Then after I update the Staging Table I want to Update last_updated from Speed_Last_Processed to the current datetime.
This way each time SP is called it's only picking up the most current records. It could be 1 or maybe 20, but that would be dictated by
last_updated from Speed_Last_Processed
Thanks so much for your help.
March 26, 2022 at 1:45 pm
Jeff,
Thanks that works perfect, but I have another table(POST Above) that I'm trying to get ALL of the records GT timestamp from processed tables: ''>'' (Select last_updated from Speed_Last_Processed)
Then after I update the Staging Table I want to Update last_updated from Speed_Last_Processed to the current datetime.
This way each time SP is called it's only picking up the most current records. It could be 1 or maybe 20, but that would be dictated by last_updated from Speed_Last_Processed
Thanks so much for your help.
How about you trying to modify Jeff's code to get the result you want, rather than expecting him to do all of the work?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 26, 2022 at 11:46 pm
Your right Phil... Sorry
I added in the other logic
Thanks Jeff
DECLARE @SQL VARCHAR(MAX) = ''
;
SELECT @SQL += REPLACE(REPLACE('
RAISERROR("-----------------------------------",0,0) WITH NOWAIT;
RAISERROR("Processing <<TableName>>",0,0) WITH NOWAIT;
INSERT INTO ajh_copy2.dbo.bc_capture_staging
([timestamp], wirespeed, equipid)
SELECT TOP 1
[timestamp] = stamp_time
,wirespeed
,equipid = SUBSTRING(REPLACE("<<TableName>>","line",""),2,128)
FROM dbo.[<<TableName>>],
Speed_Last_Processed
where stamp_time > last_updated
ORDER BY Stamp_time DESC
;' ,'"','''')
,'<<TableName>>',obj.name)
FROM sys.objects obj
WHERE obj.name LIKE 'F[0-9][0-9][0-9]%'
AND type = 'U'
AND schema_id = 1
;
EXEC(@SQL)
;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply