Hi coder I am stuck at a point and I need your help.. PFB code:
DECLARE @TableSchema sys.sysname = N'dbo';
DECLARE @TableName sys.sysname = N'x';
DECLARE @BackupTable sys.sysname = @TableName + '_' + CONVERT(VARCHAR(32), GETDATE(), 120);
DECLARE @SQL NVARCHAR(MAX) = N'
DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + ''.'' + QUOTENAME(@TableName);
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName))
BEGIN
EXEC sp_rename @TableWithSchema, @BackupTable, ''OBJECT''
END
CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '(
/* Column definitions here*/
);
';
EXEC sp_executesql
@stmt = @SQL
, @params = N'@TableSchema sys.sysname, @TableName sys.sysname, @BackupTable sys.sysname'
, @TableSchema = @TableSchema
, @TableName = @TableName
, @BackupTable = @BackupTable
;
DECLARE @CMD nvarchar(8000)
SET @CMD='BCP db..'+@TableName+' IN file path -T -f -t,-c -E'
EXEC MASTER..XP_CMDSHELL @CMD
This code is working fine but in this code suppose I have no table with name X so I m doing it for the first time, when I run it for the first time it will create a table with name X, when I run it for the second time it should update the existing one with x_getdate() but it is creating new table how can I fix this
What I have tried:
DECLARE @TableSchema sys.sysname = N'dbo';
DECLARE @TableName sys.sysname = N'x';
DECLARE @BackupTable sys.sysname = @TableName + '_' + CONVERT(VARCHAR(32), GETDATE(), 120);
DECLARE @SQL NVARCHAR(MAX) = N'
DECLARE @TableWithSchema NVARCHAR(256) = QUOTENAME(@TableSchema) + ''.'' + QUOTENAME(@TableName);
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName))
BEGIN
EXEC sp_rename @TableWithSchema, @BackupTable, ''OBJECT''
END
CREATE TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '(
/* Column definitions here*/
);
';
EXEC sp_executesql
@stmt = @SQL
, @params = N'@TableSchema sys.sysname, @TableName sys.sysname, @BackupTable sys.sysname'
, @TableSchema = @TableSchema
, @TableName = @TableName
, @BackupTable = @BackupTable
;
DECLARE @CMD nvarchar(8000)
SET @CMD='BCP db..'+@TableName+' IN file path -T -f -t,-c -E'
EXEC MASTER..XP_CMDSHELL @CMD