May 14, 2019 at 1:15 pm
If anyone ever needs to build a table on the fly and create a physical table that not there or say you are loading a table that changes a lot here is a script that will take care of this need. Something simple using the information schema tables.
IF NOT EXISTS (
SELECT 1
FROM [DataBase].sys.objects
WHERE object_id = object_id(N'[DataBase].[Schema].[Table]')
)
BEGIN
DECLARE @Table VARCHAR(255) = '[DataBase].[Schema].[Table]',
@TempTable VARCHAR(255) = '#tmp_Table',
@TableLike VARCHAR(255),
@sql VARCHAR(max)
SET @TableLike = @TempTable + '%'
SELECT @sql = 'CREATE TABLE ' + @Table + ' (' + stuff((
SELECT ', ' + cast(QUOTENAME(Column_Name) AS VARCHAR(255)) + ' ' + CASE
WHEN cast(upper(DATA_TYPE) AS VARCHAR(255)) IN (
'CHAR',
'NCHAR',
'VARCHAR',
'NVARCHAR'
)
THEN cast(upper(DATA_TYPE) AS VARCHAR(255)) + '(' + cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(255)) + ')'
WHEN cast(DATA_TYPE AS VARCHAR(255)) IN (
'DECIMAL',
'NUMERIC'
)
THEN cast(upper(DATA_TYPE) AS VARCHAR(255)) + '(' + cast(NUMERIC_PRECISION AS VARCHAR(255)) + ',' + cast(NUMERIC_SCALE AS VARCHAR(255)) + ')'
ELSE cast(upper(DATA_TYPE) AS VARCHAR(255))
END AS [text()]
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @TableLike
ORDER BY ORDINAL_POSITION
FOR XML Path('')
), 1, 2, '') + ')'
exec (@sql)
END
ELSE
BEGIN
TRUNCATE TABLE [DataBase].[Schema].[Table]
END
INSERT INTO [DataBase].[Schema].[Table]
SELECT *
FROM #tmp_Table
May 14, 2019 at 1:44 pm
Why not just
SELECT * INTO [Database].[Schema].[Table]
FROM #tmp_Table
WHERE 1 = 0
?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 15, 2019 at 12:25 am
Linked serves is the reason for this.
May 15, 2019 at 1:19 am
Where are linked servers?
No linked server is indicated in your script.
_____________
Code for TallyGenerator
May 15, 2019 at 1:25 am
In other code that I wrote above this code. This was just something pulled out of a much larger query I wrote and just sharing. I'm not going into details of the other data.
May 15, 2019 at 3:39 am
It’s hard to give a solution for an unknown task.
The overall approach could change substationslly, depending on what you’re trying to achieve.
Are you trying to create tables on remote servers?
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply