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
?
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy